January 14, 2010 at 8:28 am
Hi there,
I have the following scenario below:
CREATE TABLE #t ([Date] datetime, [Count] int, MediaBuyer varchar(50))
INSERT INTO #t VALUES ('1/7/2009', 2, 'Amanda')
INSERT INTO #t VALUES ('1/9/2009', 3, 'Amanda')
INSERT INTO #t VALUES ('1/11/2009', 4, 'Amanda')
INSERT INTO #t VALUES ('1/13/2009', 5, 'Amanda')
INSERT INTO #t VALUES ('1/15/2009', 6, 'Amanda')
INSERT INTO #t VALUES ('1/7/2009', 10, 'Carol')
INSERT INTO #t VALUES ('1/9/2009', 11, 'Carol')
INSERT INTO #t VALUES ('1/11/2009', 12, 'Carol')
INSERT INTO #t VALUES ('1/13/2009', 15, 'Carol')
INSERT INTO #t VALUES ('1/15/2009', 16, 'Carol')
SELECT * FROM #t
DROP TABLE #t
The result is displayed as:
Date CountMediaBuyer
2009-01-07 00:00:00.0002Amanda
2009-01-09 00:00:00.0003Amanda
2009-01-11 00:00:00.0004Amanda
2009-01-13 00:00:00.0005Amanda
2009-01-15 00:00:00.0006Amanda
2009-01-07 00:00:00.00010Carol
2009-01-09 00:00:00.00011Carol
2009-01-11 00:00:00.00012Carol
2009-01-13 00:00:00.00015Carol
2009-01-15 00:00:00.00016Carol
Now, the report has to be displayed in the following format:
MediaBuyer1/71/91/111/131/15
Amanda 23456
Carol 1011121516
Could this be done by using PIVOT? Please help!
January 14, 2010 at 9:42 am
I think I got to a point where I can display the result by date.
SELECT[1/7/2009],
[1/9/2009],
[1/11/2009],
[1/13/2009],
[1/15/2009]
FROM(SELECT[Date],
[Count],
MediaBuyer
FROM#t
) p PIVOT (SUM([Count])
FOR [Date]
IN ([1/7/2009], [1/9/2009], [1/11/2009], [1/13/2009], [1/15/2009])
) As pvt
I have one other column MediaBuyer which I cannot find a way to nest that inside here.
January 14, 2010 at 11:41 am
You're almos there.....
SELECT MediaBuyer,
[1/7/2009],
[1/9/2009],
[1/11/2009],
[1/13/2009],
[1/15/2009]
FROM (
SELECT [Date],
[Count],
MediaBuyer
FROM #t
) p
PIVOT (
SUM([Count])
FOR [Date] IN ([1/7/2009], [1/9/2009], [1/11/2009], [1/13/2009], [1/15/2009])
) As pvt
January 14, 2010 at 1:16 pm
Thanks John! I found an article on codeproject which helped me a lot. But thanks for your help too.
http://www.codeproject.com/KB/database/Pivot2Columns.aspx
Sid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply