With the sql query I get table 1
x
with t as(QUERY)
select Date, key, Type,
row_number ( ) over(partition by key order by Date asc) orders
from t
table 1
Date Key Type orders
12/10/2007 7 Q1 1
30/06/2015 7 W 2
21/06/2019 7 G1 3
31/01/2008 9 Q5 1
5/02/2016 9 W3 2
17/12/2019 9 G2 3
12/10/2007 10 Q12 1
5/02/2016 10 W4 2
17/12/2019 10 G6 3
Trying to pivot using window functions below on Server 2012 fails
create extension if not exists tablefunc;
select * from crosstab ($$
with t as(QUERY)
select Date, key, Type,
row_number ( ) over(partition by key order by Date asc) :: integer as orders
from t
$$) as ct (key varchar
"1" integer,
"2" integer,
"3" integer)
order by key asc
My desired output
1 2 3
7 Q1 W G1
9 Q5 W3 G2
10 Q12 W4 G6
Advertisement
Answer
Use conditional aggregation
select key,
max(case when order=1 then type end) as '1',
max(case when order=2 then type end) as '2',
max(case when order=3 then type end) as '3'
from tablename
group by key
Also, you can try with pivot –
SELECT key, piv.* FROM
(
select Date, key, Type,
row_number ( ) over(partition by key order by Date asc) orders
from t
) X
PIVOT(
max(type )
FOR orders IN (
[1], [2], [3]
)
) AS piv