使用SQL中的枢轴发出问题

Question

在查看这个示例时，我尝试使用POVIT，下面是这代码：

    CREATE DATABASE DEMO
GO
 
USE DEMO
GO
 
-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot
 
CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)
GO
 
-- Inerting some sample records
INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
GO

这里是支点选择

        -- Getting table data
    SELECT
    ItemCode, 
    ItemName, 
    ItemColour
    from TblPivot
    GO
     
    -- Getting agreegated data using Pivot and converted rows to column
    SELECT
    *
     FROM
     (
        SELECT
     ItemCode, 
     ItemName, 
     ItemColour
        FROM TblPivot
     ) AS P
    PIVOT
    (
      Count(ItemName) FOR ItemColour IN (Red, Blue, Green)// Here is the Issue 
where it knows what words to give it like Red,Blue,Green what I want is to use 
what ever the ItemColur it could be 100s What ever you get
 from the data base use that for the `IN(ItemColur)`  
    ) AS pv
    GO

它一直说，PIVOT操作符中指定的列名"ItemColur“与PIVOT参数中的现有列名相冲突。我怎么才能让它起作用，我甚至试着让临时表不工作

Answer 1

这是动态支点的代码：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ItemColour)
  FROM (SELECT distinct p.ItemColour FROM dbo.TblPivot AS p
  ) AS x;
SET @sql = N'
SELECT itemcode, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.itemcode, p.ItemName, p.ItemColour
   FROM dbo.TblPivot AS p
) AS j
PIVOT
(
  COUNT(ItemName) FOR ItemColour IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

上述查询的输出如下所示：

​

​