Elaborate on a query

SQL 2000
Current Query
SELECT YEAR(T1.RecievedDate) AS 'YEAR',
DATENAME(MONTH,T1.RecievedDate) AS 'MONTH',
COUNT(T2.LedgerKey) as 'COUNTS'
FROM BacsHdrYearly AS T1
JOIN BacsTrnYear AS T2
ON T1.LedgerKey = T2.LedgerKey
GROUP BY YEAR(T1.RecievedDate),
DATENAME(MONTH, T1.RecievedDate),
Month(T1.RecievedDate)
Order BY YEAR(T1.RecievedDate),
Month(T1.RecievedDate)
result
YEAR MONTH COUNTS
-- -- --
2004 January 115329
2004 February 115917
2004 March 132867
2004 April 159483
2004 May 118632
2004 June 130758
2004 July 122622
2004 August 124484
2004 September 127092
2004 October 124955
2004 November 160671
2004 December 175378
2005 January 131068
2005 February 132700
desired result
Year Jan Feb Mar etc
2004 115329 115917 132867
2005 131068 132700You are looking for a cross-tab ("pivot") query. The *best* place for this
is on the client/presentation tier.
http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:7C5525B8-8206-49FD-BAD1-4681C9A6A905@.microsoft.com...
> SQL 2000
> Current Query
> SELECT YEAR(T1.RecievedDate) AS 'YEAR',
> DATENAME(MONTH,T1.RecievedDate) AS 'MONTH',
> COUNT(T2.LedgerKey) as 'COUNTS'
> FROM BacsHdrYearly AS T1
> JOIN BacsTrnYear AS T2
> ON T1.LedgerKey = T2.LedgerKey
> GROUP BY YEAR(T1.RecievedDate),
> DATENAME(MONTH, T1.RecievedDate),
> Month(T1.RecievedDate)
> Order BY YEAR(T1.RecievedDate),
> Month(T1.RecievedDate)
> result
>
> YEAR MONTH COUNTS
> -- -- --
> 2004 January 115329
> 2004 February 115917
> 2004 March 132867
> 2004 April 159483
> 2004 May 118632
> 2004 June 130758
> 2004 July 122622
> 2004 August 124484
> 2004 September 127092
> 2004 October 124955
> 2004 November 160671
> 2004 December 175378
> 2005 January 131068
> 2005 February 132700
> desired result
> Year Jan Feb Mar etc
> 2004 115329 115917 132867
> 2005 131068 132700|||Aaron, Ive had a look at thoseexamples and am struggling to get to grios
with it, all i get is an error stating
'Internal SQL Server error.'
Ive tried to reduce the query to the following
SELECT YEAR(C.BhYear_RecievedDate),
'Jan' = ISNULL((Select COUNT(BHYear_transcount)
FROM BacsHdrYearly
Where Month(BhYear_RecievedDate) = 2
AND YEAR(BhYear_RecievedDate) =
YEAR(C.BhYear_RecievedDate) ),0),
'Feb' = ISNULL((Select COUNT(BHYear_transcount)
FROM BacsHdrYearly
Where Month(BhYear_RecievedDate) = 2
AND YEAR(BhYear_RecievedDate) =
YEAR(C.BhYear_RecievedDate) ),0)
FROM BacsHdrYearly AS C
GROUP BY YEAR(C.BhYear_RecievedDate)
"Aaron [SQL Server MVP]" wrote:

> You are looking for a cross-tab ("pivot") query. The *best* place for thi
s
> is on the client/presentation tier.
> http://www.aspfaq.com/2462
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:7C5525B8-8206-49FD-BAD1-4681C9A6A905@.microsoft.com...
>
>|||> 'Internal SQL Server error.'
What build # are you using? (Show the results from SELECT @.@.VERSION)
This typically indicates a bug in SQL Server, however most of these have
been patched at this point...
A|||Aaron
results from @.@.verson
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
"Aaron [SQL Server MVP]" wrote:

> What build # are you using? (Show the results from SELECT @.@.VERSION)
> This typically indicates a bug in SQL Server, however most of these have
> been patched at this point...
> A
>
>