PIVOT with groups and dynamic column names

Question

I have 3 tables that hold items properties, because each group of items can have different number of properties I hold them as key-value.

Table Package

| ID | Name    |
| 1  | TVs     |
| 2  | Laptops |

Table PackageItem

| ID | PackageId | Description  |
| 1  | 1         | Samsung TV   |
| 2  | 1         | Sony TV      |
| 3  | 2         | Apple laptop |
| 4  | 2         | Lenovo       |

Table PackageItemDetail

| ID | PackageItemId | Key               | Value    | PropertyOrder |
| 1  | 1             | Brand             | Samsung  | 1             |
| 2  | 1             | Size              | 42 inch  | 2             |
| 3  | 1             | Power consumption | A+       | 3             |
| 4  | 1             | Remote            | Smart    | 5             |
| 5  | 1             | Weight            | 15kg     | 4             |
| 6  | 2             | Brand             | Sony     | 1             |
| 7  | 2             | Size              | 50 inch  | 2             |
| 8  | 2             | Power consumption | A+++     | 3             |
| 9  | 2             | Remote            | Standard | 5             |
| 10 | 2             | Weight            | 20kg     | 4             |
| 11 | 3             | Brand             | Apple    | 1             |
| 12 | 3             | Procesor          | Intel    | 2             |
| 13 | 4             | Brand             | Lenovo   | 1             |
| 14 | 4             | Procesor          | Intel I7 | 2             |

If I select items and their properties for specific package using those queries:

SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID (NOLOCK) JOIN PackageItem PI (NOLOCK) ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=1 ORDER BY PID.PackageItemId, PID.PropertyOrder;

SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID (NOLOCK) JOIN PackageItem PI (NOLOCK) ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=2 ORDER BY PID.PackageItemId, PID.PropertyOrder;

I get those results:

Each Package always have same number or properties, so TVs have 5 properties and Laptops have 2 properties.

I'd like to transform those tables into those:

| Brand   | Size    | Power consumption | Weight | Remote   |
| Samsung | 42 inch | A+                | 15kg   | Smart    |
| Sony    | 50 inch | A+++              | 20kg   | Standard |

| Brand  | Procesor |
| Apple  | Intel    |
| Lenovo | Intel I7 |

I was able to create simple Pivot using:

SELECT
    [Brand]
   ,[Procesor]
FROM
    ( SELECT
        PI.[Id]
       ,PID.[Key]
       ,PID.[Value]
      FROM
        PackageItemDetail PID ( NOLOCK )
        JOIN PackageItem PI ( NOLOCK ) ON PID.PackageItemId = PI.Id
      WHERE
        PI.PackageId = 2
    ) AS SourceTable PIVOT
( MAX(Value) FOR [Key] IN ( [Brand], [Procesor] ) ) AS PivotTable;

but this way I must specify properties by hand, but I'd like to have them dynamic (so same query will work for different packages)

I've created SQL Fiddle with sample data: http://sqlfiddle.com/#!18/e8c51/1

If you [Search for "SQL-Server Dynamic Pivot"](https://stackoverflow.com/search?q=SQL-Server+dynamic+Pivot) you will get 3,394 results, have you read any of these? As an aside, you may want to read this - [Bad habits : Putting NOLOCK everywhere](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/) — GarethD, Feb 22 '18 at 10:24
Also, if you are saying that your categories will always contain fixed properties, i.e. TVs will always have just `Size, Power consumption, Weight, Remote`, then EAV may not even be the correct approach for you, Class table inheritance, as described [in this answer](https://stackoverflow.com/a/3579462/1048425) sounds like it is more suitable to your needs. — GarethD, Feb 22 '18 at 10:29
@GarethD didn't know about that approach. However I'm not able to change database design. By saying fixed properties I wanted to say that all items in each category will have same number of properties. There won't be an option that on TV will have 5 properties and other TV 7 properties. Number of properties can be dynamic (key-value table was the easiest to achieve this). So If one day I'll have to add another property to TVs then I'll add record to key-value table. This isn't the best solution, but as I mentioned I can't change structure right now — Misiu, Feb 22 '18 at 10:35

score 1 · Answer 1 · answered Feb 22 '18 at 10:58

I tried to apply dynamic SQL in for your problem.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Key])
FROM (SELECT DISTINCT [Key] FROM PackageItemDetail PID 
      JOIN PackageItem PI ON PID.PackageItemId = PI.Id
      WHERE PI.PackageId = 2) AS Courses
SET @DynamicPivotQuery = 
  N'SELECT
    '+ @ColumnName +'
  FROM
    ( SELECT
        PI.[Id]
       ,PID.[Key] 
       ,PID.[Value]
      FROM
        PackageItemDetail PID ( NOLOCK )
        JOIN PackageItem PI ( NOLOCK ) ON PID.PackageItemId = PI.Id
      WHERE
        PI.PackageId = 2
    ) AS SourceTable PIVOT
( MAX(Value) FOR [Key] IN (' + @ColumnName + ')) AS PivotTable'

EXEC sp_executesql @DynamicPivotQuery

Below is the link to the demo of the query:

http://sqlfiddle.com/#!18/e8c51/44

For explanation, you can go to this link:

http://sqlhints.com/tag/dynamic-pivot-column-names/

score 0 · Answer 2 · answered Feb 22 '18 at 11:38

DECLARE @COLUMN1 VARCHAR(250),@STATEMENT1 NVARCHAR(MAX),@COLUMN2 VARCHAR(250),@STATEMENT2 NVARCHAR(MAX);

SELECT @COLUMN1 = COALESCE(@COLUMN1+',','')+QUOTENAME([Key]) FROM(
SELECT DISTINCT PID.[Key] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=1) A

SET @STATEMENT1 = N'
select  *from (
SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=1 ) as a
PIVOT (MIN([Value]) FOR [Key] IN ('+@COLUMN1+'))AS P1
UNION ALL
select  *from (
SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=1 ) as a
PIVOT (MAX([Value]) FOR [Key] IN ('+@COLUMN1+'))AS P2'

--PRINT @STATEMENT1
EXEC (@STATEMENT1)

SELECT @COLUMN2 = COALESCE(@COLUMN2+',','')+QUOTENAME([Key]) FROM(
SELECT DISTINCT PID.[Key] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=2) A1

SET @STATEMENT2 = N'
select  *from (
SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=2) as a
PIVOT (MIN([Value]) FOR [Key] IN ('+@COLUMN2+'))AS P1
UNION ALL
select  *from (
SELECT PID.[Key], PID.[Value] FROM PackageItemDetail PID  JOIN PackageItem PI  ON PID.PackageItemId=PI.Id
WHERE PI.PackageId=2 ) as a
PIVOT (MAX([Value]) FOR [Key] IN ('+@COLUMN2+'))AS P2'

--PRINT @STATEMENT2
EXEC (@STATEMENT2)

OUTPUT FOR @STATEMENT1

Brand   Power consumption   Remote  Size    Weight
Samsung A+                  Smart   42 inch 15kg
Sony    A+++              Standard  50 inch 20kg

OUTPUT FOR @STATEMENT2

Brand   Procesor
Apple   Intel
Lenovo  Intel I7

PIVOT with groups and dynamic column names

2 Answers2