August 15, 2006 at 9:39 am
Hello,
I have a table like
Yr C1 C2 C3 C4 C.. Cn
2000 2 4 5 6 7 8
2001 4 5 3 9 0 10
.
200n 2 4 4 4 4 7
I want to convert it to
Column 2000 2001 . 200n
C1 2 4 2
C2 4 5 10
C3
C4
.
Cn
What will be the best way to do this conversion?
Thanks for the help.
August 15, 2006 at 1:02 pm
-- prepare test data
declare @test table (Yr int, C1 int, C2 int, C3 int, C4 int, C5 int, C6 int)
insert @test
select 2000, 2, 4, 5, 6, 7, 8 union all
select 2001, 4, 5, 3, 9, 0, 10 union all
select 2003, 2, 4, 4, 4, 4, 7
-- do the work
select 'c1' [column],
max(case when yr = 2000 then c1 end) [2000],
max(case when yr = 2001 then c1 end) [2001],
max(case when yr = 2003 then c1 end) [2003]
from @test
union all
select 'c2',
max(case when yr = 2000 then c2 end),
max(case when yr = 2001 then c2 end),
max(case when yr = 2003 then c2 end)
from @test
union all
select 'c3',
max(case when yr = 2000 then c3 end),
max(case when yr = 2001 then c3 end),
max(case when yr = 2003 then c3 end)
from @test
union all
select 'c4',
max(case when yr = 2000 then c4 end),
max(case when yr = 2001 then c4 end),
max(case when yr = 2003 then c4 end)
from @test
union all
select 'c5',
max(case when yr = 2000 then c5 end),
max(case when yr = 2001 then c5 end),
max(case when yr = 2003 then c5 end)
from @test
union all
select 'c6',
max(case when yr = 2000 then c6 end),
max(case when yr = 2001 then c6 end),
max(case when yr = 2003 then c6 end)
from @test
N 56°04'39.16"
E 12°55'05.25"
August 15, 2006 at 1:17 pm
Thanks a lot ,
if the Yr will be variable like it varies the range based on the return , how that can be handled
for example the above example is for three years and if the next query will be from 1995 to 2000.
Thanks
August 16, 2006 at 5:00 am
select 'c1' [column],
max(case when yr = 1995 then c1 end) [1995],
max(case when yr = 1996 then c1 end) [1996],
max(case when yr = 1997 then c1 end) [1997],
max(case when yr = 1998 then c1 end) [1998],
max(case when yr = 1999 then c1 end) [1999],
max(case when yr = 2000 then c1 end) [2000]
from @test-2
union all
select 'c2',
max(case when yr = 1995 then c2 end),
max(case when yr = 1996 then c2 end),
max(case when yr = 1997 then c2 end),
max(case when yr = 1998 then c2 end),
max(case when yr = 1999 then c2 end),
max(case when yr = 2000 then c2 end)
from @test-2
union all
select 'c3',
max(case when yr = 1995 then c3 end),
max(case when yr = 1996 then c3 end),
max(case when yr = 1997 then c3 end),
max(case when yr = 1998 then c3 end),
max(case when yr = 1999 then c3 end),
max(case when yr = 2000 then c3 end)
from @test-2
union all
select 'c4',
max(case when yr = 1995 then c4 end),
max(case when yr = 1996 then c4 end),
max(case when yr = 1997 then c4 end),
max(case when yr = 1998 then c4 end),
max(case when yr = 1999 then c4 end),
max(case when yr = 2000 then c4 end)
from @test-2
union all
select 'c5',
max(case when yr = 1995 then c5 end),
max(case when yr = 1996 then c5 end),
max(case when yr = 1997 then c5 end),
max(case when yr = 1998 then c5 end),
max(case when yr = 1999 then c5 end),
max(case when yr = 2000 then c5 end)
from @test-2
union all
select 'c6',
max(case when yr = 1995 then c6 end),
max(case when yr = 1996 then c6 end),
max(case when yr = 1997 then c6 end),
max(case when yr = 1998 then c6 end),
max(case when yr = 1999 then c6 end),
max(case when yr = 2000 then c6 end)
from @test-2
Or you can read my article at http://qa.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
N 56°04'39.16"
E 12°55'05.25"
August 16, 2006 at 6:08 pm
With all due respect to the efforts of Mr. Peter Larrson the be all and end all for dynamic crosstabs in ms sql server is the RAC utility @: http://www.rac4sql.net
It combines the flexibility to solve complex problems with simplicity. There is simply no more reason to reinvent the wheel nor waste time in trying code complex crosstab problems. Since RAC is a set of t-sql sp's and a few functions its integrated with engine for even further flexibility. Just imagine the simplicity of Access combined with the power of a server. And you will find much more than xtabs in RAC. RAC offers functionality like olap functions row_number, rank() and dense_rank(). And it goes further with additional ranking options not available in S2005.
Best,
August 16, 2006 at 9:17 pm
Are you suggesting that RAC is better than Reporting Services (which is free, by the way)?
--Jeff Moden
August 17, 2006 at 7:16 am
The problem which I have is to make the Year as a parmeter in the stored procedure?
Thanks,
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply