具有动态列的SQL Pivot查询

一尘不染

具有动态列的SQL Pivot查询

sql

我已经徒劳地搜索了很长时间，不得不承认失败并寻求帮助，我正在尝试修改数据透视查询，以从具有以下数据的表中生成结果的动态查询：

    UserId        PageViewed         DateTimeStamp
    1             Index.html         2011-12-01 13:55:01
    1             FAQ.html           2011-12-01 13:58:53
    1             ContactUs.html     2011-12-01 14:00:16
    2             Index.html         2011-12-01 15:55:01
    2             FAQ.html           2011-12-01 15:58:53
    2             ContactUs.html     2011-12-01 15:00:16

为了显示这样的内容，其中页码列取决于用户访问的页面数：

    User        StartTime        Page1        Page2        Page3
    1           13:55:01         Index.hml    FAQ.html     ContactUs.html
    2           15:55:01         Index.hml    FAQ.html     ContactUs.html

我已经通过对列中的硬编码进行了管理，但是显然我不想继续更改脚本以容纳越来越多的页面。

到目前为止，我有一些类似的东西：

    SELECT p.UserId, 
        CONVERT(TIME, MIN(p.DateTimeStamp), 7) StartTime,
        ISNULL(p.[1],'') Page1
        ISNULL(p.[1],'') Page2
        ISNULL(p.[1],'') Page3
    FROM
    (SELECT UserId
        ,DateTimeStamp
        ,PageViewed
        ,ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY DateTimeStamp) as pOrder
        FROM tbl) AS p
    PIVOT(MIN(PageViewed) FOR pOrder IN ([1],[2],[3]))

任何帮助或正确方向的指点将不胜感激！

提前致谢！

阅读 154

2021-03-10

共1个答案

一尘不染

我所看到的关于动态枢轴的最佳示例是Itzik Ben-
Gan的示例。这个相关的SO问题有一个很好的例子，说明您需要做什么。基本上，您需要使用一些动态sql才能实现您的目标。

tzik Ben-Gan关于如何构建动态PIVOT的示例，我强烈推荐他的Inside Microsoft SQL Server 2008：T-SQL编程书

-- Creating and Populating the Orders Table
USE tempdb;
GO

IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
orderid   int        NOT NULL PRIMARY KEY NONCLUSTERED,
orderdate datetime   NOT NULL,
empid     int        NOT NULL,
custid    varchar(5) NOT NULL,
qty       int        NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
ON dbo.Orders(orderdate, orderid);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30001, '20020802', 3, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10001, '20021224', 1, 'A', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10005, '20021224', 1, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40001, '20030109', 4, 'A', 40);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10006, '20030118', 1, 'C', 14);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20001, '20030212', 2, 'B', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40005, '20040212', 4, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20002, '20040216', 2, 'C', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30003, '20040418', 3, 'B', 15);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30004, '20020418', 3, 'C', 22);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30007, '20020907', 3, 'D', 30);
GO

-- Static PIVOT
SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN([2002],[2003],[2004])) AS P;
GO

-- Dynamic PIVOT
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.Orders) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
GO

2021-03-10