SQL数据透视表查询 [英] SQL Pivot Table Query

问题描述

我有一个像下面的表格结构

Hi,

I have a table structure like below

Date            PID     BUID    UID     Description Time 

7/30/2012	323	1	106	Code	95
7/30/2012	322	37	106	Code	3
7/30/2012	NULL	1	106	Huddle	1
7/30/2012	NULL	1	106	Team Talk
	
7/31/2012	323	1	106	Code	95
7/31/2012	322	37	106	Code	3
7/31/2012	NULL	1	106	Huddle	1
7/31/2012	NULL	1	106	Team Talk
	
8/1/2012	323	1	106	Code	95
8/1/2012	322	37	106	Code	3
8/1/2012	NULL	1	106	Huddle	1
8/1/2012	NULL	1	106	Team Talk

来自此表，我需要一个结果作为

from This table i need a result as

PID Description BUID UID 7/30/2012 7/31/2012 8/1/2012
323 Code        1    106  95           95     95
322 Code        37   106   3            3       3

以同样的方式，我需要结果

请帮助我使用数据透视表实现此目标

谢谢
Mohan

In the Same manner way i need result

Please help me to achive this using pivot table

Thanks
Mohan

推荐答案

租赁，请看下面的示例:

lease, have a look at below example:

CREATE TABLE #MyTable(mDate DATETIME, mPID INT, mBUID INT, mUID INT, mDescription NVARCHAR(30), mTime INT)

INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/30/2012', 323, 1, 106, 'Code', 95)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/30/2012', 322, 37, 106, 'Code', 3)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/30/2012', NULL, 1, 106, 'Huddle', 1)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/30/2012', NULL, 1, 106, 'Team Talk', NULL)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/31/2012', 323, 1, 106, 'Code', 95)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/31/2012', 322, 37, 106, 'Code', 3)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/31/2012', NULL, 1, 106, 'Huddle', 1)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('7/31/2012', NULL, 1, 106, 'Team Talk', NULL)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('8/1/2012', 323, 1, 106, 'Code', 95)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('8/1/2012', 322, 37, 106, 'Code', 3)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('8/1/2012', NULL, 1, 106, 'Huddle', 1)
INSERT INTO #MyTable (mDate, mPID, mBUID, mUID, mDescription, mTime)
VALUES('8/1/2012', NULL, 1, 106, 'Team Talk', NULL)


DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)

SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR(10), mDate,120)
                    FROM #MyTable
                    ORDER BY '],[' + CONVERT(NVARCHAR(10), mDate,120)
            FOR XML PATH('')),1,2,'') + ']'
--SELECT @cols

SET @dt = 'SELECT mPID, mDescription, mBUID, mUID, mDate, mTime ' +
            'FROM #MyTable ' +
            'WHERE NOT mPID IS NULL ' --+
            --'ORDER BY mPID, mDate '

SET @pt = 'SELECT mPID, mDescription, mBUID, mUID, ' + @cols + ' ' +
        'FROM (' + @dt + ') AS DT ' +
        'PIVOT(MAX(mTime) FOR mDate IN(' + @cols + ')) AS PT ' +
        'ORDER BY mPID '
EXEC(@pt)

DROP TABLE #MyTable

如您所见，我使用了临时表 [^ ](#Mytable).

As you see, i use a temporary table[^] (#Mytable).

这篇关于SQL数据透视表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！