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
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.
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'
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
