Re: Group by/Order by Pivoting problem.

From: Jörg Menker <jmenker_at_muenster.de>
Date: 6 Apr 2006 12:56:11 -0700
Message-ID: <1144353371.482107.261120@g10g2000cwb.googlegroups.com>

Hello Spongebob,

you can try something like this one below. The disadvantage is that you have to know the number of years/columns.

select x.ta_key,

	   max(case when x.nr = 3 then x.amt else null end) y1,
	   max(case when x.nr = 2 then x.amt else null end) y2,
	   max(case when x.nr = 1 then x.amt else null end) y3

from
(
select row_number() over (partition by a.key order by a.year) as nr,

	   a.key ta_key,
	   a.year,
	   a.AMT

from table_a a
) x
group by x.ta_key
order by x.ta_key

Regards,

Jörg Received on Thu Apr 06 2006 - 14:56:11 CDT