代码之家 › 专栏 › 技术社区 › Boker

带分组依据、动态列名和动态列列表的动态枢轴

t-sql sql-server

Boker · 技术社区 · 1 年前

我需要一个带有分组依据和动态列名的枢轴?

我有一个查询,它给了我这样的数据:

姓名	类型	类型2	价值观
名称1	短	A.	some_string
名称1	短	B	some_string
名称1	长	A.	some_string
名称1	长	B	some_string
名称2	短	A.	some_string
名称2	短	B	some_string
名称2	长	A.	some_string
名称2	长	B	some_string

对于这个问题,我们可以假设数据在#Test中

现在,我需要一个这样的结构:

姓名	短-A	短-B	短-C	龙A	长B	长-C
名称1	some_string	some_string	无效的	some_string	some_string	无效的
名称2	some_string	some_string	无效的	some_string	some_string	无效的

我的示例代码:

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;

类型1的列表是恒定的(总是短和长),但类型2的列表会发生变化(有时是A-C,有时是A-F等)

我以Type1为中心,但不知道接下来会发生什么

1 回复 | 直到 1 年前

Thom A 1 年前

这个假设您有一个单独的表,其中包含Types,因为您的预期结果有一个值 'C' 对于 Type2 但这不在你的数据中。你的真实桌子可能看起来不一样,但你需要为此做出调整。

正如评论中提到的, PIVOT 这里并不理想。切换到条件聚合,这要容易得多;您可以检查表中的2列是否等于类型表中的值。当然,从一开始就在应用程序层这样做很可能 更容易的 ; 如果你不明白你在用动态SQL做什么,你就不应该使用它,因为它是打破现状的好方法。

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;

db<>fiddle