SQL 2000 - group data records into one row record by specified field

[awaria]

SQL Server 2000

I have a table with records going vertically that I need to have multiple rows to end up as one record, per GLACCT, per YEAR going horizontically.

Table Structure:

GLACCT YEAR PERIOD BALANCE

100-1000 2011 1 $100
100-1000 2011 2 $150
100-2000 2011 1 $75
100-2000 2011 2 $90
ETC...

DESIRED RESULTS:

GLACCT YEAR PERIOD1 PERIOD2 PERIOD3 ETC...
100-1000 2011 $100 $150
100-2000 2011 $75 $90

Appreciate getting pointing in the right direction.

awaria

 

[gmmastros]

This is considered a PIVOT query. SQL Server 2005 has a pivot operator. Since you are using SQL2000, you'll need to do it the "old-fashioned" way, like this:

Select	GLACCT, 
        Year, 
        Min(Case When Period = 1 Then Balance End) As Period1,
        Min(Case When Period = 2 Then Balance End) As Period2,
        Min(Case When Period = 3 Then Balance End) As Period3,
        Min(Case When Period = 4 Then Balance End) As Period4
From    YourTableName
Group By GLACCT, Year

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

thanks.

I got all turned around putting the year and period in my case statement, then grouping by GLACCT.

I'm trying to setup a query for a trend report where the user wants to put in a starting year and period, then have the report display 13 columns of balances.

If they input year 2010 and period 2, the want the 1st period field to be Feb 2010s balance, then the next column be Mar 2010, and on for 13 periods ending with Year 2011, period 2. So the columns or field will span across two years to include the scope of 13 periods.

How would you approach this?

awaria

 

[markros]

You can generate your query dynamically then using a simple loop.

See the sample ideas in

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns

(your case is close to the second problem).

PluralSight Learning Library

 

[awaria]

Thanks for the link.

It looks good.

Thanks again,

awaria