How to convert rows into columns in sql server?
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
ServiceName is dynamic value.
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?
You need to
UNPIVOT and then
;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:
- I have no idea where
QuoteServiceIDfits in from your question.
- I assumed that this is all of the data. You have to explicitly name the columns twice, and the
ServiceNamevalues 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.
- 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
CASEexpression or charindex against a hard-coded list, but you’d have to manually specify the columns a 3rd time.