1

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?

3
  • Share DDL of the tables. Did you get some errors or bad results? Jan 30 '17 at 14:48
  • 2
    Where on earth does QuoteServiceID come from? Can you make sure the DDL and sample code you posted actually works as posted? Jan 30 '17 at 15:33
  • stackoverflow.com/a/15745076/3270427
    – McNets
    Jan 30 '17 at 15:39
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.