非聚合的动态枢轴 [英] dynamic pivot for non aggregate

问题描述

我知道如何动态旋转显示，例如，这里.

I know how to dynamically pivot as show, for example, here.

动态数据透视在我的场景中可能不起作用，其中 #source 可以包含动态列和动态列值，必须按照此处所示进行透视:

Dynamic pivot might not work in my scenario, where the #source can contain dynamic columns and dynamic column values, which have to be pivoted as indicated here:

IF OBJECT_ID('tempdb..#Source') IS NOT NULL DROP TABLE #Source
IF OBJECT_ID('tempdb..#Aim') IS NOT NULL DROP TABLE #Aim

CREATE TABLE #Source
(
    ColumnName NVARCHAR(10),
    ColumnValue NVARCHAR(10),
    Id INT
)

CREATE TABLE #Aim
(
    Id INT,
    Column1 NVARCHAR(10),
    Column2 NVARCHAR(10),
    Column3 NVARCHAR(10)
)

INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column1', 'Value1', 1);
INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column2', 'Value1', 1);
INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column1', 'Value2', 2);
INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column1', 'Value4', 3);
INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column3', 'Value1', 3);
INSERT INTO #Source (ColumnName, ColumnValue, Id) VALUES ('Column3', 'Value3', 4);

SELECT * FROM #Source

INSERT INTO #Aim (Id, Column1, Column2, Column3) VALUES (1, 'Value1', 'Value1', NULL)
INSERT INTO #Aim (Id, Column1, Column2, Column3) VALUES (2, 'Value2', NULL, NULL)
INSERT INTO #Aim (Id, Column1, Column2, Column3) VALUES (3, 'Value4', NULL, 'Value1')
INSERT INTO #Aim (Id, Column1, Column2, Column3) VALUES (4, NULL, NULL, 'Value3')

SELECT * FROM #Aim 

我正在尝试实现 #Aim 的最后一个 select 语句的输出.我希望这是不言自明的.这可能吗?

I am trying to achieve the output of the last select statement for #Aim. I hope this is self explanatory. Is this possible?

https:///www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

推荐答案

你可以这样做

DECLARE @col_list VARCHAR(max) = (SELECT Quotename(ColumnName) + ','
   FROM   #Source
   GROUP  BY ColumnName
   ORDER  BY ColumnName
   FOR xml path('')) -- To create the dynamic column list 

SET @col_list = LEFT(@col_list, Len(@col_list) - 1) -- To remove the leading comma 

DECLARE @sql NVARCHAR(max)

SELECT @sql = 'select * from #Source
pivot (max(ColumnValue) for ColumnName in ('
              + @col_list + '))pv'

EXEC Sp_executesql @sql  -- Execute the dynamic sql

结果:

╔════╦═════════╦═════════╦═════════╗
║ Id ║ Column1 ║ Column2 ║ Column3 ║
╠════╬═════════╬═════════╬═════════╣
║  1 ║ Value1  ║ Value1  ║ NULL    ║
║  2 ║ Value2  ║ NULL    ║ NULL    ║
║  3 ║ Value4  ║ NULL    ║ Value1  ║
║  4 ║ NULL    ║ NULL    ║ Value3  ║
╚════╩═════════╩═════════╩═════════╝

这篇关于非聚合的动态枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！