Encore un autre Pivot SQL

Question rapide sur le pivotement. J'ai datatables suivantes:

c1 | c2 | c3 | c4 | c5 pk1 | r12 | r13 | r14 | r15 pk2 | r22 | r23 | r24 | r25 pk3 | r32 | r33 | r34 | r35 

Je voudrais le faire pivoter comme suit

 [c1] | pk1 | pk2 | pk3 c2 | r12 | r22 | r32 c3 | r13 | r23 | r33 c4 | r14 | r24 | r34 c5 | r15 | r25 | r35 

Dans mon exemple, la colonne de cas c1 est une key primaire. Merci d'avance pour votre aide.

PS: Je vais donner un autre coup jusque-là.

Vous pouvez UNPIVOT puis table PIVOT . Ceci est une version de hardcode .

 DECLARE @SampleData AS TABLE ( c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ) INSERT INTO @SampleData VALUES ('pk1', 'r12','r13','r14','r15'), ('pk2', 'r22','r23','r24','r25'), ('pk3', 'r32','r33','r34','r35') ;with pvt AS ( SELECT c1, ColName, Value FROM ( SELECT * FROM @SampleData sd ) src UNPIVOT ( Value FOR ColName IN (c2,c3,c4,c5) ) pvt ) SELECT ColName AS [c1], [pk1], [pk2], [pk3] FROM ( SELECT * FROM pvt p ) src PIVOT ( Max(Value) FOR c1 IN ([pk1], [pk2], [pk3]) ) pvt 

Lien de démo: Rextester

C'est un peu long et moche, mais il fera pivoter vos données.

 WITH pk1 AS ( SELECT c1,c2 'pk1' FROM Table_1 WHERE c1 = 'pk1' UNION SELECT c1,c3 'pk1' FROM Table_1 WHERE c1 = 'pk1' UNION SELECT c1,c4 'pk1' FROM Table_1 WHERE c1 = 'pk1' UNION SELECT c1,c5 'pk1' FROM Table_1 WHERE c1 = 'pk1'), pk1_rn AS (SELECT ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY pk1 ASC) AS Row#, pk1 FROM pk1), pk2 AS ( SELECT c1,c2 'pk2' FROM Table_1 WHERE c1 = 'pk2' UNION SELECT c1,c3 'pk2' FROM Table_1 WHERE c1 = 'pk2' UNION SELECT c1,c4 'pk2' FROM Table_1 WHERE c1 = 'pk2' UNION SELECT c1,c5 'pk2' FROM Table_1 WHERE c1 = 'pk2'), pk2_rn AS (SELECT ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY pk2 ASC) AS Row#, pk2 FROM pk2), pk3 AS ( SELECT c1,c2 'pk3' FROM Table_1 WHERE c1 = 'pk3' UNION SELECT c1,c3 'pk3' FROM Table_1 WHERE c1 = 'pk3' UNION SELECT c1,c4 'pk3' FROM Table_1 WHERE c1 = 'pk3' UNION SELECT c1,c5 'pk3' FROM Table_1 WHERE c1 = 'pk3'), pk3_rn AS (SELECT ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY pk3 ASC) AS Row#, pk3 FROM pk3) SELECT pk1_rn.pk1,pk2_rn.pk2,pk3_rn.pk3 FROM pk1_rn JOIN pk2_rn ON pk1_rn.Row# = pk2_rn.Row# JOIN pk3_rn ON pk1_rn.Row# = pk3_rn.Row#