SQL Rows to Columns

Question

I have below table structure in MS SQL

AirQuoteID        Name        SalesValue
7                  M          49.50
7                  N          23.10
7                 +45         233.20
7                 +100        233.20
7                 +250        2333.10

I want a query which can return

AirQuoteID      M        N        +45        +100    +250
7               49.50    23.10    233.20     233.20   2333.10

What will be the optimum solution. The Values are dynamic

Answer 1

You need to look at the PIVOT operator.

;With T As
(
SELECT 7 AirQuoteID,'M' Name,49.50 SalesValue UNION ALL
SELECT 7 AirQuoteID,'N', 23.10 UNION ALL
SELECT 7 AirQuoteID,'+45',233.20 UNION ALL
SELECT 7 AirQuoteID,'+100',233.20 UNION ALL
SELECT 7 AirQuoteID,'+250',2333.10
)
SELECT AirQuoteID, [M], [N], [+45], [+100], [+250] 
FROM T
PIVOT
(
MAX(SalesValue)
FOR Name IN ([M], [N], [+45], [+100], [+250])
) AS pvt;

However if the values for the columns are not fixed you will need to use dynamic SQL.

Answer 2

As the data is dynamic, pivot wont help

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables link posted by @Martin really helped.

first create the below procedure

CREATE PROCEDURE [dbo].[crosstab] 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

print ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), [pivot]) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), [pivot]) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO

The article didnt mention to retrieve data from same table, so below is how you do it

EXECUTE crosstab 'select titles.AirQuoteID from AirSaleQuoteRateSlab titles 
                    inner join 
                    (select distinct AirQuoteID,[Name] from AirSaleQuoteRateSlab) sales 
                    on (sales.AirQuoteID=titles.AirQuoteID) 
                    group by titles.AirQuoteID', 'AVG(titles.SalesValue)','titles.Name','AirSaleQuoteRateSlab titles'

Answer 3

select AirQuoteID,
sum(case Name when 'M' then SalesValue else 0 end) 'M',
sum(case Name when 'N' then SalesValue else 0 end) 'N',
sum(case Name when '+45' then SalesValue else 0 end) '+45',
sum(case Name when '+100' then SalesValue else 0 end) '+100',
sum(case Name when '+250' then SalesValue else 0 end) '+250'
from Table1
group by AirQuoteID