I need something like a pivot with group by and dynamic column names?
I have a query which gives me data like this:
| Name | Type | Type2 | Value |
|---|---|---|---|
| name1 | Short | A | some_string |
| name1 | Short | B | some_string |
| name1 | Long | A | some_string |
| name1 | Long | B | some_string |
| name2 | Short | A | some_string |
| name2 | Short | B | some_string |
| name2 | Long | A | some_string |
| name2 | Long | B | some_string |
For this question, we can assume that the data is in #Test
Now, I need to get a structure like this:
| Name | Short-A | Short-B | Short-C | Long-A | Long-B | Long-C |
|---|---|---|---|---|---|---|
| name1 | some_string | some_string | NULL | some_string | some_string | NULL |
| name2 | some_string | some_string | NULL | some_string | some_string | NULL |
Code for my example:
create table Test
(
name varchar(20) not null
, Type1 varchar(20) not null
, Type2 varchar(20) not null
, Value varchar(50) null
)
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'A', 'loreipsum1')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'B', 'loreipsum2')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'A', 'loreipsum3')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'B', 'loreipsum4')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'A', 'loreipsum5')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'B', 'loreipsum6')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'A', 'loreipsum7')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'B', 'loreipsum8')
select * into #Data from dbo.Test;
select x.*
into #Typ1
from (select 'Short' Typ
union
select 'Long' Typ
) x;
select x.*
into #Typ2
from (select 'A' Typ
union
select 'B' Typ
union
select 'C' Typ
) x;
declare @PivotQuery nvarchar(max)
, @Typ1 nvarchar(max)
, @Typ2 nvarchar(max);
select @Typ1 = string_agg (QUOTENAME (Typ), ', ') from #Typ1;
select @Typ2 = string_agg (QUOTENAME (Typ), ', ') from #Typ2;
set @PivotQuery = 'select *
into #X
from
(
select name, type1, type2, value from #Data
) as SRC
pivot
(
max(Value)
for Type1 in (' + @Typ1 + ')
) as PVT1;
select * from #X;';
EXECUTE sp_executesql @PivotQuery;
List of Type1 is constant (always Short and Long) but list of Type2 will by changed (sometimes will A-C, sometimes A-F etc.)
I got pivoted by Type1 but no idea whats next
6
This assumes you have a separate table with the Types in it, as your expected results has a value for 'C' for Type2 but that isn’t in your data. Your real table might look different, but you’ll need to adjust for that.
As mentioned in the comments, PIVOT isn’t ideal here. Switch to conditional aggregation, and this is much easier. Of course, doing this in the application layer to start with is likely even easier; if you don’t understand what you’re doing with dynamic SQL you shouldn’t be using it, as it’s a great avenue for breaking things.
CREATE TABLE dbo.Type (TypeType int,
TypeName varchar(10));
INSERT INTO dbo.Type (TypeType,TypeName)
VALUES (1,'Long'),
(1,'Short'),
(2,'A'),
(2,'B'),
(2,'C'),
(2,'D'),
(2,'E'),
(2,'F');
GO
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter char(3) = N',' + @CRLF;
SELECT @SQL = N'SELECT Name,' + @CRLF +
STRING_AGG(N' MAX(CASE WHEN Type1 = ' + QUOTENAME(T1.TypeName,'''') + N' AND Type2 = ' + QUOTENAME(T2.TypeName,'''') + N' THEN Value END) AS ' + QUOTENAME(T1.Typename + N'.' + T2.TypeName),@Delimiter) WITHIN GROUP (ORDER BY T1.TypeName, T2.TypeName) + @CRLF +
N'FROM dbo.Test' + @CRLF +
N'GROUP BY Name' + @CRLF +
N'ORDER BY Name;'
FROM dbo.Type T1
CROSS JOIN dbo.Type T2
WHERE T1.TypeType = 1
AND T2.TypeType = 2;
EXEC sys.sp_executesql @SQL;