基于 SQL 中的分组列在列上透视值

2022-01-22 00:00:00 pivot sql tsql sql-server

我想根据组将值转置到列.但是，我事先并不知道这些值.

I want to pivot values to columns based on a group. However, I do not know the values beforehand.

一个查询给了我这个结果.

A query gives me this result.

Id          Code         EntityId
----------- ------------ -------------
3           22209776     1
4           143687971    3
4           143687971    4
4           143687971    5
4           143687971    15
5           143658155    7
5           143658155    8

我想输出这个

Id          Code         EntityId1     EntityId2     EntityId3     EntityId4
----------- ------------ ------------- ------------- ------------- -------------
3           22209776     1             NULL          NULL          NULL
4           143687971    3             4             5             15
5           143658155    7             8             NULL          NULL

推荐答案

如果你现在知道结果中有多少列，你需要使用动态 T-SQL 语句来构建 PIVOT.例如:

If you do now know how many column you are going to have in the result, you need to use dynamic T-SQL statement to build the PIVOT. For example:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN;
    DROP TABLE #DataSource;
END;

CREATE TABLE #DataSource
(
    [id] INT
   ,[Code] INT
   ,[EntityId] INT
);

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@Columns NVARCHAR(MAX);

DECLARE @MaxColumns INT;

INSERT INTO #DataSource ([id], [Code], [EntityId])
VALUES (3, 22209776 , 1)
      ,(4, 143687971, 3)
      ,(4, 143687971, 4)
      ,(4, 143687971, 5)
      ,(4, 143687971, 15)
      ,(5, 143658155, 7)
      ,(5, 143658155, 8)
      ,(4, 143687971, 25)
      ,(4, 143687971, 26);

-- we need to know how many columns are going to be shown
SELECT TOP 1 @MaxColumns = COUNT(*)
FROM #DataSource
GROUP BY [Code]
ORDER BY COUNT(*) DESC;

-- we are building here the following string '[1],[2],[3],[4],[5],[6]'; 
-- this will change depending the input data
WITH gen AS 
(
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 
    FROM gen 
    WHERE num+1<=@MaxColumns
)
SELECT @Columns = STUFF
(
    (
        SELECT ',[EntityId' + CAST([num] AS VARCHAR(12)) + ']'
        FROM gen
        FOR XML PATH(''), TYPE

    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
)
OPTION (maxrecursion 10000);

SET @DynamicTSQLStatement = N'
SELECT *
FROM 
( 
    SELECT [id]
          ,[Code]
          ,[EntityId]
          ,''EntityId'' + CAST(ROW_NUMBER() OVER(PARTITION BY [Code] ORDER BY [EntityId]) AS VARCHAR(12))
    FROM #DataSource
) DS ([id], [Code], [EntityId], [RowID])
PIVOT 
(
    MAX([EntityId]) for [RowID] in (' + @Columns +')
) PVT;';

EXEC sp_executesql @DynamicTSQLStatement;

相关文章