Transposing Rows to Column Headings In SQL

Question

I need help on transposing some of the rows in a column to column headings below is the example tables - the first one is how it looks now and the second one is how i want it to be transposed. Need helps and ideas please.

Tried Pivot code but not working

Manager Director ManagerID  MetricName   Numerator Denominator
Name1    Name1    1112           Metric1    24  32
Name1    Name1    1112           Metric2    26  32
Name1    Name1    1112           Metric3    45  56
Name2    Name2    1245           Metric1    78  80
Name2    Name2    1245           Metric2    90  78
Name2    Name2    1245           Metric3    34  36

Need this in this format

Manager Director ManagerID Metric1Numerator Metric1Denminator Metric3Num
Name1    Name1    1112      24            32        26
Name2    Name2    1245      78            90        90

Can be done with conditional aggregation. If the list of `MetricName` values is not fixed, use dynamic sql which is dbms specific. Tag your dbms please. — Serg, Jul 02 '19 at 08:44
Did you check the many, many [answers](https://stackoverflow.com/questions/tagged/sql+pivot) regarding this? — , Jul 02 '19 at 09:05
If you tried something, then please **[edit]** your question (by clicking on the [edit] link below it) and show us the code you have tried and the error message you got ([edit] your question, do not post code in comments) — , Jul 02 '19 at 09:06
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — JIJOMON K.A, Jul 02 '19 at 10:39

score 0 · Answer 1 · edited Jul 02 '19 at 10:37

0

Use PIVOT and UNPIVOT to convert rows into columns and vice-versa.

Try This :-

SELECT * FROM 
(SELECT manager, director, managerID,  metricname, numerator
FROM <your_table>) 
PIVOT (SUM(numerator) as nmt FOR (metricname) IN( 'Metric1', 'Metric2', 'Metric3')) 
ORDER BY manager;

edited Jul 02 '19 at 10:37

JIJOMON K.A

1,290
3
12
29

answered Jul 02 '19 at 10:19

Sunil

1
1

score 0 · Answer 2 · answered Jul 02 '19 at 10:54

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 (Manager VARCHAR(10),Director VARCHAR(10),ManagerID INT,MetricName VARCHAR(10),Numerator INT,Denominator INT)

INSERT INTO #temp VALUES
('Name1','Name1',1112,'Metric1',24,32),
('Name1','Name1',1112,'Metric2',26,32),
('Name1','Name1',1112,'Metric3',45,56),
('Name2','Name2',1245,'Metric1',78,80),
('Name2','Name2',1245,'Metric2',90,78),
('Name2','Name2',1245,'Metric3',34,36)

SELECT pvt.* 
FROM (
SELECT Manager
    ,Director
    ,ManagerID
    ,MetricName+Col AS [MetricName]
    ,Val 
FROM #temp
UNPIVOT (Val FOR Col IN([Numerator],[Denominator]))a )unpiv
PIVOT ( MAX(Val) FOR MetricName IN ([Metric1Numerator],[Metric1Denominator],[Metric2Numerator],[Metric2Denominator],[Metric3Numerator],[Metric3Denominator])) pvt

Transposing Rows to Column Headings In SQL

2 Answers2