将动态占位符列添加到动态旋转的数据中 [英] add dynamic placeholder columns to dynamically pivoted data

问题描述

此问题是继此一个之后的问题.以下 Sql 工作正常:

This questions follows on from this one. The following Sql work fine:

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);

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')

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

挑战是如果少于 n 列，我想添加虚拟列.因此，对于 n = 5，我想将其作为输出实现:

The challenge is that I want to add dummy column if there are less than n columns. So for n = 5 I would like to achieve this as output:

Id  Column1 Column2 Column3 Dummy4  Dummy5
1   Value1  Value1  NULL    NULL    NULL
2   Value2  NULL    NULL    NULL    NULL
3   Value4  NULL    Value1  NULL    NULL
4   NULL    NULL    Value3  NULL    NULL

理想情况下，这是否可能以 n 作为变量?

Is this possible ideally with n as a variable?

推荐答案

这是一种方法

DECLARE @n INT = 5
DECLARE @cnt INT = (SELECT Count(DISTINCT ColumnName)
   FROM   #Source)
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)

WHILE @cnt + 1 <= @n -- To create the dummy list based on existing count 
  BEGIN
      SET @col_list+=',[dummy' + Cast(@cnt + 1 AS VARCHAR(100)) + ']'
      SET @cnt = @cnt + 1
  END

DECLARE @sql NVARCHAR(max)

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

--PRINT @sql

EXEC Sp_executesql @sql 

结果:

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

这篇关于将动态占位符列添加到动态旋转的数据中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！