SQL Some vertical to horizontal field names/values

Question

What I've read here doesn't quite answer what I'm trying to do. That's why I'm posting a request here and I'm confident to learn what I can do on this subject.

Here are my tables:

Customer:

| ID | Firstname | Lastname |
|----|-----------|----------|
|  1 | John      | Lennon   |
|  2 | Patti     | Smith    |

LinkedTable:

| ID | TableName |
|----|-----------|
|  1 | Customer  |

LinkedTableField:

| ID | LinkedTableID | FieldName  | FieldCaption |
|----|---------------|------------|--------------|
|  1 |             1 | SocksColor | Socks Color  |
|  2 |             1 | Married    | Married?     |

LinkedTableFieldValue:

| ID | LinkedTableID | LinkedTableFieldID | OwnerID | STRValue |
|----|---------------|--------------------|---------|----------|
|  1 |             1 |                  1 |       1 | Blue     |
|  2 |             1 |                  1 |       2 | Purple   |
|  3 |             1 |                  2 |       1 | Yes      |
|  4 |             1 |                  2 |       2 | No       |

I've built this SQL View by this code:

SELECT LinkedTable.ID,
       LinkedTableField.ID,
       LinkedTableField.FieldName,
       LinkedTableField.FieldCaption,
       LinkedTableFieldValue.ID,
       LinkedTableFieldValue.OwnerID,
       LinkedTableFieldValue.STRValue 
  FROM ( (LinkedTable INNER JOIN LinkedTableField 
                              ON LinkedTable.ID = LinkedTableField.LinkedTableID
         ) INNER JOIN LinkedTableFieldValue ON 
             ( (LinkedTableField.ID = LinkedTableFieldValue.LinkedTableFieldID) 
           AND (LinkedTableField.LinkedTableID = LinkedTableFieldValue.LinkedTableID)
             )
        ) as View1;

The displaying data of View1 is:

| LinkedTableID | LinkedTableFieldID | LinkedTableFieldValueID | FieldName  | FieldCaption | OwnerID | STRValue |
|---------------|--------------------|-------------------------|------------|--------------|---------|----------|
|             1 |                  1 |                       1 | SocksColor | Socks Color  |       1 | Blue     |
|             1 |                  1 |                       2 | SocksColor | Socks Color  |       2 | Purple   |
|             1 |                  2 |                       3 | Married    | Married?     |       1 | Yes      |
|             1 |                  2 |                       4 | Married    | Married?     |       2 | No       |

Now, I can list Customer table linked to the View1 by this SQL View:

 SELECT Customer.*, 
        View1.LinkedTableID, 
        View1.FieldName, 
        View1.STRValue
   FROM (Customer LEFT JOIN View1 ON Customer.ID = View1.OwnerID) AS View2
  WHERE View1.LinkedTableID = 1;

Here is the View2 result:

| ID | Firstname | Lastname | FieldName  | STRValue |
|----|-----------|----------|------------|----------|
|  1 | John      | Lennon   | SocksColor | Blue     |
|  2 | Patti     | Smith    | SocksColor | Purple   |
|  1 | John      | Lennon   | Married    | Yes      |
|  2 | Patti     | Smith    | Married    | No       |

At this level, I'm not able to write SQL code to obtain this kind of result:

| ID | Firstname | Lastname | SocksColor | Married |
|----|-----------|----------|------------|---------|
|  1 | John      | Lennon   | Blue       | Yes     |
|  2 | Patti     | Smith    | Purple     | No      |

At least, is there a way to write a SQL code to display this kind of list under PostgreSQL?

It would be great!

Thank you for your help.

Does this answer your question? [Pivot table on postgresql](https://stackoverflow.com/questions/70036450/pivot-table-on-postgresql) — nbk, May 04 '22 at 15:52
Unfortunately not. Your link shows what I've already read. My request is like a partial pivot table as I've tried to explain on this post. Anyway, thank you. — Laurent, May 04 '22 at 16:35
from your view, you can make with my link or any other that explains crosstable, do your result with ease — nbk, May 04 '22 at 16:48
@nbk Thank you for your help. You were right for the links. I've found exactly what I needed. I thought the crosstab creates a full pivot and what I was looking for was limited to a partial pivot. — Laurent, May 06 '22 at 08:30

Laurent · Answer 1 · 2022-05-06T09:41:37.350

Here is the solution under postgres

CREATE TABLE Contacts (
   CID       integer,
   FirstName text,
   Lastname  text
);

CREATE TABLE LkTables (
   TID        integer,
   TableName text
);

CREATE TABLE LkFields (
   FID         integer,
   LkTablesFID integer,
   FieldName   text,
   Caption     text
);

CREATE TABLE LkValues (
   VID         integer,
   LkTablesVID integer,
   LkFieldsVID integer,
   OwnerID     integer,
   STRValue    text
);

INSERT INTO Contacts VALUES 
   (1, 'John', 'Lennon'),
   (2, 'Patti', 'Smith'),
   (3, 'Eric', 'Clapton'),
   (4, 'Marc', 'Knopfler');
   
INSERT INTO LkTables VALUES
   (1, 'Contacts'),
   (2, 'Items');
   
INSERT INTO LkFields VALUES
   (1, 1, 'SocksColor', 'Socks Color'),
   (2, 1, 'Married', 'Married?'),
   (3, 1, 'KidsNumber', 'Number of Kids');
   
INSERT INTO LkValues VALUES
   (1, 1, 1, 1, 'Blue'),
   (2, 1, 1, 2, 'Purple'),
   (3, 1, 2, 1, 'Yes'),
   (4, 1, 2, 2, 'No'),
   (5, 1, 3, 1, '4'),
   (6, 1, 3, 2, '1'),
   (7, 1, 3, 4, '7'),
   (8, 1, 2, 3, 'Yes');

CREATE VIEW view1 AS
SELECT *
  FROM ( (LkTables INNER JOIN LkFields 
                              ON LkTables.TID = LkFields.LkTablesFID
         ) INNER JOIN LkValues ON 
             ( (LkFields.FID = LkValues.LkFieldsVID) 
           AND (LkFields.LkTablesFID = LkValues.LkTablesVID)
             )
        ) c;
        
CREATE VIEW view2 AS
SELECT *
  FROM (Contacts LEFT JOIN view1 ON Contacts.CID = View1.OwnerID);
  
-- the result
SELECT * 
  FROM CROSSTAB(
       'SELECT lastname, firstname, caption, strvalue FROM view2 ORDER BY 2,3',
       $$VALUES ('Socks Color'::text), ('Married?'), ('Number of Kids')$$) AS ct ("LastName" text, "Firstname" text, "Socks Color" text, "Married" text, "Kids" text);

Visual result

| LastName | Firstname | Socks Color | Married | Kids |
|----------|-----------|-------------|---------|------|
| Clapton  | Eric      | null        | Yes     | null |
| Lennon   | John      | Blue        | Yes     | 4    |
| Knopfler | Marc      | null        | null    | 7    |
| Smith    | Patti     | Purple      | No      | 1    |

SQL Some vertical to horizontal field names/values

1 Answers1