SQL Server – How to Convert Rows into Columns Using Pivot or Without Pivot

sql serversql-server-2008sql-server-2008-r2

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?

Best Answer

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.
Related Question