Prev: Sql Server / Access query source code
Next: Bad column prefix, why does it work? (2000)
From: Chamark via SQLMonster.com on 23 Mar 2010 10:02
Using SQL2005 - Beginner Level

Table 1 called Months2010
Columns - Month, BD, ED
Example January 01/01/2010 01/31/2010

Table 2 called CM
Columns - CMDate, CallID
Example - 01/02/2010 123

SELECT Months2010.Month, COUNT(CM.CallID) AS Calls
FROM CM CROSS JOIN
Months2010
WHERE CMDATE BETWEEN BD AND ED
GROUP BY Months2010.Month, BD, ED
ORDER BY BD

Result gives me

Month Calls
January 123456
February 654321
March 12345

I would like to be able to have the results loolk like this:

January February March
Calls 123456 654321 12345

Can PIVOT do this? If so how? If not how else can I do this? Any help is
greatly appreciated.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1

From: Plamen Ratchev on 23 Mar 2010 10:23
Try this:

SELECT January, February, March, April, May, June,
July, August, September, October, November, December
FROM (
SELECT M.[month], COUNT(C.CallID) AS calls
FROM CM AS C
JOIN Months2010 AS M
ON C.cmdate BETWEEN M.bd AND M.ed
GROUP BY M.[month], M.bd, M.ed) AS T
PIVOT
(SUM(calls) FOR [month] IN
(January, February, March, April, May, June,
July, August, September, October, November, December)) AS P;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Chamark via SQLMonster.com on 23 Mar 2010 10:48
Plamen - You are always so helpful. Thank you so much again for your
assistance. You are the BEST!!

Plamen Ratchev wrote:
>Try this:
>
>SELECT January, February, March, April, May, June,
> July, August, September, October, November, December
>FROM (
>SELECT M.[month], COUNT(C.CallID) AS calls
>FROM CM AS C
>JOIN Months2010 AS M
> ON C.cmdate BETWEEN M.bd AND M.ed
>GROUP BY M.[month], M.bd, M.ed) AS T
>PIVOT
>(SUM(calls) FOR [month] IN
>(January, February, March, April, May, June,
> July, August, September, October, November, December)) AS P;
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1

 | 
Pages: 1
Prev: Sql Server / Access query source code
Next: Bad column prefix, why does it work? (2000)