table row values for column headers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

table row values for column headers?

Author

Topic

snowbunny
Starting Member

5 Posts

Posted - 2009-03-17 : 13:59:58

Hi.

I have two tables in a simple database:
Prices
ID (int, identity, PK)
Year (smallint - year of value)
Quarter (smallint - year quarter 1-4)
Area (smallint - FK to Areas.ID)
Index (float)

Areas
ID (int, identity, PK)
Name (varchar)

So, as you can see, the Prices table contains price information over time for certain areas. This might look like this:

Prices
ID Year Quarter Area Index
1 2000 1 1 100
2 2000 2 1 110
3 2000 3 1 112
4 2000 4 1 115
5 2000 1 2 200
6 2000 2 2 210
7 2000 3 2 212
8 2000 4 2 215

Areas
ID Name
1 North
2 South

Now, what I would like to have returned from a query is the following:

Year Quarter North South
2000 1 100 200
2000 2 110 210
2000 3 112 212
2000 4 115 215

so, in other words, the row names from areas become column names.

I'm sure this must be possible. Could someone point me in the correct direction?

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-17 : 15:29:27

If there are no too many distinct values for 'Area' maybe u can use this...

declare @t table (id int,yr int,qrtr int, area int, indx int)
insert @t
select 1, 2000, 1, 1, 100 union all
select 2, 2000, 2, 1, 110 union all
select 5, 2000, 1, 2, 200 union all
select 6, 2000, 2, 2, 210

select yr,qrtr,max(case when area = 1 then indx else 0 end) as North,max(case when area = 2 then indx else 0 end) as South
from @t group by yr,qrtr

snowbunny
Starting Member

5 Posts

Posted - 2009-03-17 : 15:45:10

Thanks for your reply, but that's not quite what I want... the areas table is updatable, so the hard-coding way you suggest won't be scalable.

I need some way of creating columns for each entry of the areas table at execution time.

matty
Posting Yak Master

161 Posts

Posted - 2009-03-18 : 03:21:28

You can use PIVOT..
SELECT Yr,Quarter,MAX([North]) AS North,MAX([South]) AS South
FROM
(
SELECT p.Yr,p.Quarter,p.area,p.indx,a.ID,a.Name
FROM Prices p JOIN Areas a ON p.area = a.ID
)T
pivot
(
MAX(indx)
FOR Name IN ([North],[South])
) AS pvt
GROUP BY Quarter,Yr

You may have to use dynamic sql if there are distinct values for 'Area'

snowbunny
Starting Member

5 Posts

Posted - 2009-03-18 : 04:15:49

Thanks for your reply. Again, I need to do this at execution time, without knowing the contents of the Area table. I suppose this could be achieved by building a SQL string and using spExecuteSQL, but unfortunately for this project, I am also restricted to SQL Server 2000, so PIVOT isn't available. I should have said that in my original question, sorry.

matty
Posting Yak Master

161 Posts

Posted - 2009-03-18 : 05:37:23

Refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216&whichpage=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources