By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
create table TestTable (process_id varchar(10), process_info varchar(100), [value] int, [date] varchar(7))
insert into TestTable select 'A', 'chop', 3, '2020-05'
insert into TestTable select 'A', 'chop', 4, '2020-07'
insert into TestTable select 'A', 'extrude', 6, '2020-01'
insert into TestTable select 'B', 'something',2, '2019-52'
insert into TestTable select 'B', 'something',4, '2020-05'
5 rows affected
DECLARE @cols NVARCHAR(MAX), @cols_to_display NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols_to_display = STUFF(
(
SELECT DISTINCT
/*','+QUOTENAME(t.[date])+ ' as '''+replace(t.[date], '-', '_')+'_values'''*/ --with NULLs in the output
', isnull(convert(varchar(100),'+QUOTENAME(t.[date])+ '),'''') as '''+replace(t.[date], '-', '_')+'_values''' --with NULLs removed
FROM TestTable t FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, ''
);
SET @cols = STUFF(
(
SELECT DISTINCT
','+QUOTENAME(t.[date])
FROM TestTable t FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, ''
);
SET @query = 'SELECT process_id, process_info, '+@cols_to_display+'
from (
SELECT process_id, process_info, [value], [date]
FROM TestTable
)x
pivot
(
SUM([value]) for [date] in ('+replace(@cols, '_values', '')+')
) pvt';
print @query
EXECUTE (@query);
---------------------------------------------------
---METHOD 2
---------------------------------------------------
SET @cols = STUFF((select distinct ',
ISNULL(CONVERT(VARCHAR(100), SUM(CASE WHEN [date]=''' + CAST([date] as varchar(10)) + ''' THEN [value] END)), '''') AS [' + CAST(replace([date], '-', '_') as varchar(10)) + '_values]'
FROM TestTable
FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @query = 'SELECT process_id, process_info, ' + @Cols + ' FROM TestTable group by process_id, process_info'
| process_id | process_info | 2019_52_values | 2020_01_values | 2020_05_values | 2020_07_values |
|---|---|---|---|---|---|
| A | chop | 3 | 4 | ||
| A | extrude | 6 | |||
| B | something | 2 | 4 |
SELECT process_id, process_info, isnull(convert(varchar(100),[2019-52]),'') as '2019_52_values', isnull(convert(varchar(100),[2020-01]),'') as '2020_01_values', isnull(convert(varchar(100),[2020-05]),'') as '2020_05_values', isnull(convert(varchar(100),[2020-07]),'') as '2020_07_values'
from (
SELECT process_id, process_info, [value], [date]
FROM TestTable
)x
pivot
(
SUM([value]) for [date] in ([2019-52],[2020-01],[2020-05],[2020-07])
) pvt
| process_id | process_info | 2019_52_values | 2020_01_values | 2020_05_values | 2020_07_values |
|---|---|---|---|---|---|
| A | chop | 3 | 4 | ||
| A | extrude | 6 | |||
| B | something | 2 | 4 |
Warning: Null value is eliminated by an aggregate or other SET operation.