How can I convert an EAV schema to a normal schema using PIVOT?

I have a table called SourceTable, in that I have 4 fields. Properties_title field it has got 3 values (AAA,BBB,CCC) but can also have more. Depending on each of them, NumericValue field and Property_item_title field has a value.According to the table in the below, if Properties_title be AAA or CCC so Property_item_title it has value and if Properties_title be BBB so NumericValue it has value. Now I want pivot this to make just one row for each W_ID like Result Table.

SourceTable:

+--------+------------------+---------------+---------------------+
|  W_ID  | Properties_title | NumericValue  | Property_item_title |
+--------+------------------+---------------+---------------------+
| 102859 |     AAA          | null          |  Useless            |
| 102859 |     BBB          | 30000         |  null               |
| 102859 |     CCC          | null          |  Repair             |
| 92527  |     AAA          | null          |  Use                |
| 92527  |     BBB          | 3250          |  null               |
+--------+------------------+---------------+---------------------+

Result Table:

+-------+-----------+---------+---------+
|  W_id |   AAA     |  BBB    | CCC     |
+-------+-----------+---------+-------- +
|102859 |  Useless  | 30000   |  Repair |
|92527  |  Use      | 3250    |  null   |
|...    |    ...    | ...     |  ...    |
+-------+-----------+---------+---------+

the column names has to be dynamic

My Code:

CREATE TABLE dbo.SourceTable (W_ID int NOT NULL,
                            Properties_title varchar(3) NOT NULL,
                            NumericValue int NULL,
                            Property_item_title varchar(100) NULL);

INSERT INTO dbo.SourceTable
VALUES (102859,'AAA',NULL,'Useless'),
       (102859,'BBB',30000,NULL),
       (102859,'CCC',NULL,'Repair'),
       (92527,'AAA',NULL,'Use'),
       (92527,'BBB',3250,NULL);

SELECT *
FROM dbo.SourceTable;

Here is a db<>fiddle.

Thank you for your help.

Answer

To pivot over a fixed list of columns, you can do conditional aggregation:

select 
    w_id,
    max(case when properties_title = 'AAA' then property_item_title end) aaa,
    max(case when properties_title = 'BBB' then numeric_value end) bbb,
    max(case when properties_title = 'CCC' then property_item_title end) ccc
from sourcetable
group by w_id

Advertisement

Answer