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 @querySo 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!
1条答案
按热度按时间pgky5nke1#
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:
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.