MS SQL Pivot Dynamic Headers

Question

I have the following two tables:

Serial  Key     Value
ABC     Attr1   ABC1
ABC     Attr2   ABC2
ABC     Attr3   ABC3
XYZ     Attr1   XYZ1
XYZ     Attr2   XYZ2
XYZ     Attr3   XYZ3

and

Key     Include IncludeOrder
Attr1   Y       2
Attr2   NULL    NULL
Attr3   Y       1

The first table is the data, the second table is the data Keys to include on the report.

I want to generate the following from a query:

Serial  Attr3   Attr1
ABC     ABC3    ABC1
XYZ     XYZ3    XYZ1

I know this will require a pivot, with a GROUP BY on Serial, but I haven't been able to complete the subquery to pull the columns designated by Include or sort them to be ordered correctly in the output by IncludeOrder.

could you post this as text instead of images? Makes it a LOT easier for others to work with. — Sean Lange, Oct 26 '17 at 14:46
see accepted answer from [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — CoridRocket, Oct 27 '17 at 07:56

Lukasz Szozda · Accepted Answer · 2017-10-26T16:02:55.417

2

It is very easy to achieve with dynamic-SQL:

DECLARE @sql NVARCHAR(MAX)= 'SELECT Serial, <cols> FROM t GROUP BY Serial;';

DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(CONCAT('MIN(CASE WHEN [key] = ''',
                      [key],
                       ''' THEN [value] END) AS ',
                       QUOTENAME([key])),
                       ',') WITHIN GROUP (ORDER BY IncludeOrder)
FROM r
WHERE Include = 'Y';

SET @sql = REPLACE(@sql, '<cols>', @cols);

SELECT @sql;
EXEC(@sql);

Output query:

SELECT Serial,
       MIN(CASE WHEN [key] = 'Attr3' THEN [value] END) AS [Attr3],
       MIN(CASE WHEN [key] = 'Attr1' THEN [value] END) AS [Attr1] 
FROM t 
GROUP BY Serial;

DBFiddle Demo

Notes:

SQL Server 2017 and above (because of STRING_AGG, feel free to rewrite it to XML+STUFF version)
conditional aggregation with MIN

edited Oct 26 '17 at 16:02

answered Oct 26 '17 at 15:56

Lukasz Szozda

162,964
23
234
275

Unfortunately I have to work with Server 2014... is there a similar way to achieve this? I've not worked with the XML path queries yet. – Mooseman Oct 26 '17 at 16:02
@Mooseman Time to learn new thing :) If you don't know how to follow use simple while loop to implement string concatenation. I will leave it for you as exercise :) – Lukasz Szozda Oct 26 '17 at 16:03

MS SQL Pivot Dynamic Headers

1 Answers1