CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
bat man
bat man

Reputation: 33

How to transform columns and rows?

I can't transform one column to multiple columns and multiple columns to rows.

The best way to explain myself would be with the table:

Table

Upvotes: 1

Views: 60

Answers (1)

MJoy
MJoy

Reputation: 1369

You could use the UNPIVOT and PIVOT functions in SQL server to transpose the rows & columns, The following query should do what you want:

CREATE TABLE #temp (Category VARCHAR(10), [% SKU] INT, [% Aveg Inv] INT, [% Inv Val] INT, [% Shipped Cases] INT, [% Inv Cub] INT, [% Sales] INT)

INSERT INTO #temp VALUES
('Type1',10,4,4,14,0,7),
('Type2',82,30,30,30,0,68),
('Type3',3,1,1,0,0,0),
('Type4',6,65,65,56,0,24)


SELECT pvt.* FROM
(
SELECT [Category], [Value], [Driver]
FROM #temp t
UNPIVOT (Value FOR Driver IN ([% SKU],[% Aveg Inv],[% Inv Val],[% Shipped Cases],[% Inv Cub],[% Sales])) a) unpvt

PIVOT (SUM([Value]) FOR [Category] IN ([Type1],[Type2],[Type3],[Type4])) pvt

ORDER BY CASE Driver
            WHEN '% SKU' THEN 1
            WHEN '% Aveg Inv' THEN 2
            WHEN '% Inv Val' THEN 3
            WHEN '% Shipped Cases' THEN 4
            WHEN '% Inv Cub' THEN 5
            WHEN '% Sales' THEN 6 END

The result is as below,

Driver          Type1   Type2   Type3   Type4
% Aveg Inv      4       30      1       65
% Inv Cub       0       0       0       0
% Inv Val       4       30      1       65
% Sales         7       68      0       24
% Shipped Cases 14      30      0       56
% SKU           10      82      3       6

Upvotes: 2

Related Questions