Multi Dynamic Pivot in SQL Server

Question

I have data that is entered into a table in this way:

id	id_group	timestamp	operation	outcome
2	1	2023-05-23 17:45:10.5930086	Mariage 1	1
4	1	2023-05-23 17:45:11.8111470	Avvitatura 3	1
6	1	2023-05-23 17:45:13.1847870	Piantaggio 3	1
7	1	2023-05-23 17:45:13.8604394	Tenuta 3	1
10	1	2023-05-23 17:45:15.6856890	Etichettatura 3	0

I would like to be able to flatten the different rows into one, grouping them by the value of the group_id column and consistently renaming the column names to achieve this:

id_group	Mariage 1 id	Mariage 1 timestamp	Mariage 1 outcome	Avvitatura 3 id	Avvitatura 3 timestamp	Avvitatura 3 outcome	Piantaggio 3 id	Piantaggio 3 timestamp	Piantaggio 3 outcome	Tenuta 3 id	Tenuta 3 timestamp	Tenuta 3 outcome	Etichettatura 3 id	Etichettatura 3 timestamp	Etichettatura 3 outcome
1	2	2023-05-23 17:45:10.5930086	1	4	2023-05-23 17:45:11.8111470	1	6	2023-05-23 17:45:13.1847870	1	7	2023-05-23 17:45:13.8604394	1	10	2023-05-23 17:45:15.6856890	0

After several attempts, I came up with a query like the following, but got stuck at the point where I have to rename the columns. As long as I use their original names (Variable @cols) I can get results, but if I use @cols1 or @cols2 to discriminate timestamp columns from the outcomes ones I only get NULL values

DECLARE @cols AS NVARCHAR(MAX), @cols1 AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME([operation]) FROM [test].[dbo].[operations_assy] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT @cols1 = STUFF((SELECT DISTINCT ',' + QUOTENAME([operation] + '_OP') FROM [test].[dbo].[operations_assy] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SELECT @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME([operation] + '_TS') FROM [test].[dbo].[operations_assy] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = '
SELECT [id_group], ' + @cols1 + ',' + @cols2 + ' FROM
( SELECT [operation], [id_group], [outcome], CONVERT(VARCHAR(10), [timestamp], 120) AS [timestamp] FROM [test].[dbo].[operations_assy] ) AS x
PIVOT ( MAX([outcome]) FOR [operation] IN (' + @cols1 + ') ) AS p1
PIVOT ( MAX([timestamp]) FOR [timestamp] IN (' + @cols2 + ') ) AS p2
ORDER BY [id_group]'

SELECT @query

So my main problem is: how can I discriminate multiple pivots for different columns so that there are no duplicates? Any suggestions on how to solve this? Thanks!

Answer 1

Another day, another pivot question. I wonder why you want to do this stuff in SQL, since it's hard to handle this kind of output later.

Anyways, i suggest you skip the actual pivot operator and do it the old fashioned way:

select *
into #t
from 
(
    VALUES  (2, 1, N'2023-05-23 17:45:10.5930086', N'Mariage 1', 1)
    ,   (4, 1, N'2023-05-23 17:45:11.8111470', N'Avvitatura 3', 1)
    ,   (6, 1, N'2023-05-23 17:45:13.1847870', N'Piantaggio 3', 1)
    ,   (7, 1, N'2023-05-23 17:45:13.8604394', N'Tenuta 3', 1)
    ,   (10, 1, N'2023-05-23 17:45:15.6856890', N'Etichettatura 3', 0)
) t (id,id_group,timestamp,operation,outcome)

DECLARE @cols AS NVARCHAR(MAX), @cols1 AS NVARCHAR(MAX), @cols2 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT '
    ,   max(case when operation = ''' + operation + ''' then id end) as ' + QUOTENAME([operation] + ' id')  + N'
    ,   max(case when operation = ''' + operation + ''' then timestamp end) as ' + QUOTENAME([operation] + ' timestamp')  + N'
    ,   max(case when operation = ''' + operation + ''' then outcome end) as ' + QUOTENAME([operation] + ' outcome' )
    FROM #t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = '
SELECT [id_group] ' + @cols + ' FROM #t
group by id_group
ORDER BY [id_group]'

SELECT @query
exec(@query)

drop table #t

This creates conditional values for every column with the desired alias name.

Note: If you have single quotes in "operation" column, you need to do: REPLACE(operation, '''', '''''') in the + operation + part.