SQL PIVOT-WINDOW FUNCTIONS

With the sql query I get table 1

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

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