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

Question

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.

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Panagiotis Kanavos, Dec 11 '19 at 13:30
Why doesn't it, @JavedAbedi ? I see no obvious reason why it does not. — Thom A, Dec 11 '19 at 13:36
@larnu Because I have two filed with value and the code should recognize which value is for which column. — Javad Abedi, Dec 11 '19 at 13:38
That statement makes no sense. That suggested duplicate, to me, looks like it would work with an `ISNULL`. — Thom A, Dec 11 '19 at 13:42
WOW! This isn't pivoting at all. This is an Entity-Attribute-Value schema, with different fields per type for an extra twist. The required query is trying to convert it back to a proper schema, because otherwise it's unqueryable. This can't be done with pivoting. — Panagiotis Kanavos, Dec 11 '19 at 13:48
This is far more difficult than dynamic pivoting. For the query to work dynamically, ihe query has to check the *properties* to see which is null or not, and use the `title` field afterwards, as a column name. Perhaps *two* column queries, to get the `NumeriValue` and `Item_Title` columns separately? And hope there are no duplicates. Then two separate PIVOTs for each column type? — Panagiotis Kanavos, Dec 11 '19 at 13:52

score 2 · Accepted Answer · answered Dec 11 '19 at 13:11

2

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

answered Dec 11 '19 at 13:11

GMB

216,147
25
84
135

1

Whoever downvoted, PIVOT doesn't require *less* code than conditional aggregation, nor is it better or faster in any way – Panagiotis Kanavos Dec 11 '19 at 13:12
1

You have hardcoded column headers. Assuming OP has posted sample data, this query will not work when there is a fourth value say, "DDD" – Pratik Bhavsar Dec 11 '19 at 13:13
@PratikBhavsar: indeeed, this is why I mentioned that this pivots over a *fixed* list of columns (`PIVOT` has the same limitation). OP did not state that column names had to be dynamic. And for this to be dynamic, we would need dynamic SQL, which seems beyond the scope of the question. – GMB Dec 11 '19 at 13:15
1

@PratikBhavsar that's what PIVOT does too. There's no way to use PIVOT on a query without knowing the column names in advance. In fact, there's no way to write *any* query without knowing the columns in advance. `*` is just a shortcut returning all *column* names – Panagiotis Kanavos Dec 11 '19 at 13:18
@gmb exactly my friend, the column names had to be dynamic – Javad Abedi Dec 11 '19 at 13:20
@PratikBhavsar the title says PIVOT. PIVOT requires fixed columns. If the OP wanted something different, the question should have made that explicit. – Panagiotis Kanavos Dec 11 '19 at 13:24
@JavadAbedi that's not what you asked in the question though. There are a *lot* of duplicate questions that show how to use dynamic SQL for "dynamic" pivots. You can't use PIVOT without knowing the number of columns. – Panagiotis Kanavos Dec 11 '19 at 13:24
@PanagiotisKanavos Based on OPs recent comment, I have modified the question. – Pratik Bhavsar Dec 11 '19 at 13:25
this will do it https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – JonWay Dec 11 '19 at 14:16

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

1 Answers1

Linked