Trasposizione Righe in Colonne

[sspintux]

...
avrei fatto cosi (avendo letto anche il precedente thread )
.....

.

... allora aggiungiamone anche un'altra più leggibile che fa uso delle CTE
e compendia sia la TOP N che la Traslazione dele righe in colonne

use tempdb
go


-- Tabella di partenza
if OBJECT_ID('t') is not null drop table t
go
create table t (IDAz int, mat varchar(4))
insert into t (IDAz,Mat) values (1,'m11')
insert into t (IDAz,Mat) values (1,'m12')
insert into t (IDAz,Mat) values (1,'m13')
insert into t (IDAz,Mat) values (1,'m14')
insert into t (IDAz,Mat) values (1,'m15')
insert into t (IDAz,Mat) values (1,'m16')
insert into t (IDAz,Mat) values (1,'m16')


insert into t (IDAz,Mat) values (2,'m21')
insert into t (IDAz,Mat) values (2,'m22')
insert into t (IDAz,Mat) values (2,'m23')


insert into t (IDAz,Mat) values (3,'m31')
insert into t (IDAz,Mat) values (3,'m32')


go
              
with 
 b (idaz,mat,rowid) as
( 
  select q.IDAz,q.mat,q.rowid 
  from (
	    select IDAz,mat, 
	           ROW_NUMBER() OVER (PARTITION BY idaz ORDER BY mat) as rowid 
	    FROM  t
	   ) q
  where q.rowid<=5
 ),
 a(idaz) as
 ( 
  select distinct IDAz from b
 )
select  a.idaz
       ,(select b.mat from b where b.IDAz=a.idaz and b.rowid=1) as mat1 
       ,(select b.mat from b where b.IDAz=a.idaz and b.rowid=2) as mat2 
       ,(select b.mat from b where b.IDAz=a.idaz and b.rowid=3) as mat3 
       ,(select b.mat from b where b.IDAz=a.idaz and b.rowid=4) as mat4 
       ,(select b.mat from b where b.IDAz=a.idaz and b.rowid=5) as mat5 
from a