SQL Pivot Table Query

Question

0.00/5 (No votes)

See more:

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

In the Same manner way i need result

Please help me to achive this using pivot table

Thanks
Mohan

Posted 29-Oct-12 23:36pm

V Mohan

Updated 30-Oct-12 6:41am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-10-30T07:16:00

lease, have a look at below example:

SQL

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

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