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.

Is this for reporting purposes? If so, this should be done in the reporting tool. If not, your first challenge is getting the column names as data values. Are columns C1-C5 fixed? Can these be hard coded as rows? Do you have a fixed number of rows in this table? You're saying that if you have a thousand rows then you want to transform into a thousand columns — Nick.Mc
– Nick.Mc, Commented Apr 19, 2017 at 22:46
The columns are fixed(50 of them); on the other hand the rows are flexible and not known in advance(yes 1000 rows will produce 1000 columns with 50 rows) — herme 0
– herme 0, Commented Apr 20, 2017 at 12:53

TriV · Accepted Answer · 2017-04-20 03:17:08Z

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

Jason · Accepted Answer · 2017-04-19 23:46:14Z

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#

Collectives™ on Stack Overflow

Yet another SQL Pivot

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related