如何透视数据-解网

问：

PK_ID	HEADER2	HEADER3	标题 4
1	SEQ_NO	1223910	一个
2	SCRAP_TYPE	C	一个
3	SCRAP_HIST_ID	6306713	一个
4	LOT_TRANS_ID	6306713	一个
5	LOT_NO	编号：231NB0012	一个
6	PROC_ID	光盘	一个
7	SEQ_NO	1223911	一个
8	SCRAP_TYPE	C	一个
9	SCRAP_HIST_ID	6309120	一个
10	LOT_TRANS_ID	6309120	一个
11	LOT_NO	编号：231NB0013	一个
12	PROC_ID	光盘	一个
...	...	....	一个

我需要使用上面的表数据运行数据透视表，

由于HEADER2的规律性，我想将HEADER2透视到列，HEADER3透视到 ROW，但我不确定如何设置条件和旋转透视。

不同的标头是动态输入的，数据也像上表一样输入，那么我应该怎么做

结果是使用切换大小写语法派生的，但派生了一个标头和数据。

DECLARE @SN INT
DECLARE @ST INT
DECLARE @SH INT
DECLARE @LTI INT 
DECLARE @LT INT
DECLARE @PI INT 


SET @SN = 0
SET @ST = 0
SET @SH = 0
SET @LTI = 0
SET @LT  = 0
SET @PI = 0

SELECT TOP 10000 MIN(SEQ_NO) AS 'SEQ_NO', MIN(SCRAP_TYPE) AS 'SCRAP_TYPE', MIN(SCRAP_HIST_ID) AS 'SCRAP_HIST_ID' , MIN(LOT_TRANS_ID) AS 'LOT_TRANS_ID' , MIN(LOT_NO) AS 'LOT_NO', MIN(PROC_ID) AS 'PROC_ID'
FROM ( SELECT CASE WHEN HEADER2 ='SEQ_NO' THEN 'SEQ_NO' END AS SEQ_NO
            , CASE WHEN HEADER2 ='SCRAP_TYPE' THEN 'SEQ_NO' END AS SCRAP_TYPE
            , CASE WHEN HEADER2 ='SCRAP_HIST_ID' THEN 'SEQ_NO' END AS SCRAP_HIST_ID
            , CASE WHEN HEADER2 ='LOT_TRANS_ID' THEN 'SEQ_NO' END AS LOT_TRANS_ID
            , CASE WHEN HEADER2 ='LOT_NO' THEN 'SEQ_NO' END AS LOT_NO  
            , CASE WHEN HEADER2 ='PROC_ID' THEN 'SEQ_NO' END AS PROC_ID
            , CASE WHEN HEADER2 = 'SEQ_NO' THEN                             @SN +1
                   WHEN HEADER2 = 'SCRAP_TYPE' THEN                         @ST+1
                   WHEN HEADER2 = 'SCRAP_HIST_ID' THEN @SH + 1
                   WHEN HEADER2 = 'LOT_TRANS_ID' THEN @LTI + 1
                   WHEN HEADER2 = 'LOT_NO' THEN @LT + 1
                   WHEN HEADER2 = 'PROC_ID' THEN @PI + 1 END AS ROWNUMBER
       FROM #temptable
       UNION ALL 
       SELECT CASE WHEN HEADER2 ='SEQ_NO' THEN HEADER3 END AS SEQ_NO
            , CASE WHEN HEADER2 ='SCRAP_TYPE' THEN HEADER3 END AS SCRAP_TYPE
            , CASE WHEN HEADER2 ='SCRAP_HIST_ID' THEN HEADER3 END AS SCRAP_HIST_ID
            , CASE WHEN HEADER2 ='LOT_TRANS_ID' THEN HEADER3 END AS LOT_TRANS_ID
            , CASE WHEN HEADER2 ='LOT_NO' THEN HEADER3 END AS LOT_NO  
            , CASE WHEN HEADER2 ='PROC_ID' THEN HEADER3 END AS PROC_ID
            , CASE WHEN HEADER2 = 'SEQ_NO' THEN @SN+1
                   WHEN HEADER2 = 'SCRAP_TYPE' THEN @ST+1
                   WHEN HEADER2 = 'SCRAP_HIST_ID' THEN @SH + 1
                   WHEN HEADER2 = 'LOT_TRANS_ID' THEN @LTI + 1
                   WHEN HEADER2 = 'LOT_NO' THEN @LT + 1
                   WHEN HEADER2 = 'PROC_ID' THEN @PI + 1 END AS ROWNUMBER
       FROM #temptable
       ) SUB

sql-server t-sql 透视

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(HEADER2), ', ') WITHIN GROUP (ORDER BY HEADER2 DESC)
FROM (SELECT DISTINCT HEADER2 FROM #temptable) AS Headers;

SET @sql = '
SELECT ' + @columns + '
FROM (
    SELECT HEADER2, HEADER3 FROM #temptable
) AS Source
PIVOT (
    MIN(HEADER3) FOR HEADER2 IN (' + @columns + ')
) AS PivotTable;';

EXEC sp_executesql @sql;

上述查询动态识别表列中的唯一头，并使用这些头构造查询，并通过动态 SQL 执行。在此处查看示例。HEADER2#temptablePIVOT

如何透视数据

How to pivot data

评论

评论