Yet another SQL Pivot

Question

Quick question about pivoting. I have the following data:

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

I would like to rotate it as follow

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

In my example, case column c1 is a primary key. Thank you in advance for you help.

PS: I will give it another go until then.

Answer 1

You could UNPIVOT then PIVOT table. This is a hardcode version.

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

Demo link: Rextester

Answer 2

This is kind of long and ugly, but it will pivot your data.

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#