How to convert rows into columns in sql server using pivot or with out pivot

Question

How to convert rows into columns in sql server?

This is my query output: enter image description here

I need like..

ServiceName CLO IC AC
--------------------- --------- ----------- -----------
HeatExchanged 27933.562 133217.4152 146385.4261
MTD 74.07 57.82 55.17
PerfTube_PressureDrop 3.83 / 0 1.21 / 0 2.63 / 0
Design_TubeOD 0.75 0.625 0.625
Design_NoTubes 7 41 32

Here ServiceName is dynamic value.

I tried:

select QuoteServiceID,HeatExchanged, MTD, PerfTube_PressureDrop,
 Design_TubeOD, Design_NoTubes
from
(
 select QuoteServiceID, HeatExchanged
 from Quotes_Output_Thermal
) d
pivot
(
 max(QuoteServiceID)
 for HeatExchanged in (QuoteServiceID,HeatExchanged, MTD,
 PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
) piv; 

Here are the DDL commands

CREATE TABLE Quotes_Output_Thermal 
(
 ServiceName nvarchar(50) NULL,
 HeatExchanged nvarchar(50) NULL,
 MTD nvarchar(50) NULL, 
 PerfTube_PressureDrop nvarchar (50) NULL,
 Design_TubeOD nvarchar (50) NULL, 
 Design_NoTubes int NULL 
);
INSERT dbo.Quotes_Output_Thermal
(
 ServiceName, HeatExchanged, MTD, PerfTube_PressureDrop, 
 Design_TubeOD, Design_NoTubes
)
values('CLO','27933.562', '74.07', '3.83 / 0', '0.75', 7),
 ('IC', '133217.4152','57.82', '1.21 / 0 ','0.625',41),
 ('AC', '146385.4261','55.17', '2.63 / 0', '0.625',32);

Help me, how can I achieve this?

• Paweł Tajs: Share DDL of the tables. Did you get some errors or bad results?
• Aaron Bertrand: Where on earth does QuoteServiceID come from? Can you make sure the DDL and sample code you posted actually works as posted?
• McNets: stackoverflow.com/a/15745076/3270427

Answer 1

You need to UNPIVOT and then PIVOT.

;WITH x AS 
(
 SELECT ServiceName, num, sn 
 FROM (
 SELECT ServiceName, 
 HeatExchanged = CONVERT(varchar(32), HeatExchanged),
 MTD = CONVERT(varchar(32), MTD),
 PerfTube_PressureDrop = CONVERT(varchar(32), PerfTube_PressureDrop),
 Design_TubeOD = CONVERT(varchar(32), Design_TubeOD),
 Design_NoTubes = CONVERT(varchar(32), Design_NoTubes)
 FROM dbo.Quotes_Output_Thermal
 ) AS d 
 UNPIVOT (num FOR sn IN 
 (HeatExchanged, MTD, PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
 ) AS unp
)
SELECT ServiceName = sn, CLO, IC, AC FROM x
PIVOT (MAX(num) FOR ServiceName IN (CLO,IC,AC)) AS p;

Though wanting a different set of values called "ServiceName" in the output might be confusing for consumers of the query.

Couple of caveats:

1. I have no idea where QuoteServiceID fits in from your question.
2. I assumed that this is all of the data. You have to explicitly name the columns twice, and the ServiceName values once. If you have 40 more service names and a bunch of additional measurement columns, you can use dynamic SQL to build those commands - but that is an order of magnitude more complicated.
3. There is no straightforward way to get the ordering you showed in the question - the (un)pivoting will yield seemingly arbitrary results that don't match the physical ordering of the columns in the table. You can use a CASE expression or charindex against a hard-coded list, but you'd have to manually specify the columns a 3rd time.