Help SQL Crosstab Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help SQL Crosstab Query

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-09-15 : 12:13:06

Hi Guys,

It's been a little while since my last post. I'm working on my personal project and I got stucked on something. I want to do a crosstab/matrix style query. Below is the data. Note: there is no primary key in the table

DATA:
QUARTER | YEAR | PERCENTAGE
4 2007 67
3 2008 73
1 2008 52
2 2008 34
3 2008 31

THIS IS THE RESULT THAT I WANTED TO LOOK LIKE.
QUARTER | YEAR | PERCENTAGE1 | PERCENTAGE2
4 2007 67 NULL
1 2008 NULL 52
2 2008 73 34
3 | 2008 | NULL | 31

This is my SQL query.
SELECT QUARTER, YEAR, PERCENTAGE FROM Quarter

I tried to follow the tutoria posted in the article section, but no luck.

I hope someone here can help me.

Thanks in advanced.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 12:30:17

your sample output does not make much sense. can you explain how you think the output will be obtained in words?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-17 : 06:12:23

Hi,

Try with this

Create Table #Table ( Id Int, Quarter Int, YEAR Int, PERCENTAGE Int)
Insert into #Table (Quarter , YEAR , PERCENTAGE)
Select 4, 2007, 67 Union All
Select 3, 2008, 73 Union All
Select 1, 2008, 52 Union All
Select 2, 2008, 34 Union All
Select 3, 2008, 31

UPDATE T1
SET Id = (SELECT COUNT(*) FROM #Table T WHERE T.Quarter = T1.Quarter AND T.YEAR = T1.YEAR AND T.PERCENTAGE <= T1.PERCENTAGE )
FROM #Table T1

Declare @i int, @str varchar(8000)
Select @i = min(id) From #table Group By year
Select @str = ''

While ( @I is not null)
Begin
Select @str = @str + ', Percentage' + cast (@i as varchar(10) ) + ' = min(case when id = ' + cast (@i as varchar(10) ) + ' then PERCENTAGE end)'
Select @i = min(id) From #table where id > @i

End
Select @str = 'select Quarter, Year ' + @str + 'from #table group by Quarter, year'
Print @str
Exec (@str)

drop table #table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 06:31:14

quote:
Originally posted by ranganath

Hi,

Try with this

Create Table #Table ( Id Int, Quarter Int, YEAR Int, PERCENTAGE Int)
Insert into #Table (Quarter , YEAR , PERCENTAGE)
Select 4, 2007, 67 Union All
Select 3, 2008, 73 Union All
Select 1, 2008, 52 Union All
Select 2, 2008, 34 Union All
Select 3, 2008, 31

UPDATE T1
SET Id = (SELECT COUNT(*) FROM #Table T WHERE T.Quarter = T1.Quarter AND T.YEAR = T1.YEAR AND T.PERCENTAGE <= T1.PERCENTAGE )
FROM #Table T1

Declare @i int, @str varchar(8000)
Select @i = min(id) From #table Group By year
Select @str = ''

While ( @I is not null)
Begin
Select @str = @str + ', Percentage' + cast (@i as varchar(10) ) + ' = min(case when id = ' + cast (@i as varchar(10) ) + ' then PERCENTAGE end)'
Select @i = min(id) From #table where id > @i

End
Select @str = 'select Quarter, Year ' + @str + 'from #table group by Quarter, year'
Print @str
Exec (@str)

drop table #table

have you checked the sample output posted by OP. your query above gives below output

Quarter     Year        Percentage1 Percentage2
----------- ----------- ----------- -----------
4           2007        67          NULL
1           2008        52          NULL
2           2008        34          NULL
3           2008        31          73

this is different from what OP asked for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources