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,'') + ']'
SET @dt = 'SELECT mPID, mDescription, mBUID, mUID, mDate, mTime ' +
'FROM #MyTable ' +
'WHERE NOT mPID IS NULL '
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
As you see, i use a
temporary table[
^] (
#Mytable
).