Convert Rows to columns and Columns to rows without pivot

Question

ProductCode YTMAch  YTMTg   MTDTg   YTDPer  MTDPer
PrimaxX         0   0         0        0    0
SuperGrip       0   0         0        0    0
WC              0   0         0        0    0
WP              0   0         0        0    0

I want to convert this rows into columns and columns into rows

check this once https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — author4, Jul 19 '18 at 09:52

score 2 · Accepted Answer · answered Jul 19 '18 at 10:01

One way would be using case and apply

Create table #temp (ProductCode varchar(20), YTMAch int,  YTMTg int,  MTDTg int,  YTDPer int,  MTDPer int)

Insert into #temp values
('PrimaxX'  , 1, 2 , 3, 4, 5),
('SuperGrip', 1, 2 , 3, 4, 5),
('WC'       , 1, 2 , 3, 4, 5),
('WP'       , 1, 2 , 3, 4, 5)

Select id,
  max(case when ProductCode = 'PrimaxX' then value end) PrimaxX,
  max(case when ProductCode = 'SuperGrip' then value end) SuperGrip,
  max(case when ProductCode = 'WC' then value end) WC,
  max(case when ProductCode = 'WP' then value end) WP
from 
(Select * from #temp cross apply (values ('YTMAch',YTMAch), 
                                         ('YTMTg',YTMTg),   
                                         ('MTDTg',MTDTg),   
                                         ('YTDPer',YTDPer),  
                                         ('MTDPer',MTDPer))v(id,value))c 
group by id

LukStorms · Answer 2 · 2018-07-19T12:15:23.210

In standard SQL you could use UNION ALL with selects for each column.
But it's a rather wordy method.

A database specific method like Ajay's solution for MS Sql Server should be more concise.

For example:

select Metric, 
sum(case when ProductCode = 'PrimaxX' then Value end) as PrimaxX,
sum(case when ProductCode = 'SuperGrip' then Value end) as SuperGrip,
sum(case when ProductCode = 'WC' then Value end) as WC,
sum(case when ProductCode = 'WP' then Value end) as WP
from
(
  select 'YTMAch' as Metric, ProductCode, YTMAch as Value from yourtable
  union all 
  select 'YTMTg', ProductCode, YTMTg from yourtable
  union all 
  select 'MTDTg', ProductCode, MTDTg from yourtable
  union all 
  select 'YTDPer', ProductCode, YTDPer from yourtable
  union all 
  select 'MTDPer', ProductCode, MTDPer from yourtable
) q
group by Metric;

Convert Rows to columns and Columns to rows without pivot

2 Answers2