Friday, July 25, 2014

Transpose of Rows to Columns in SQL Server

-- Transpose of a Records in SQL Server

IF Object_id('tempdb.dbo.#temp')IS NOT NULL
BEGIN
DROP TABLE #temp
END

CREATE TABLE #temp
(
year INT,
qtr VARCHAR(20),
saleamt INT
)

INSERT INTO #temp
SELECT 2011,
'Q1',
15

INSERT INTO #temp
SELECT 2012,
'Q2',
16

INSERT INTO #temp
SELECT 2012,
'Q3',
25

INSERT INTO #temp
SELECT 2012,
'Q4',
35

INSERT INTO #temp
SELECT 2012,
'Q5',
45

INSERT INTO #temp
SELECT 2011,
'Q2',
112

INSERT INTO #temp
SELECT 2011,
'Q3',
125

INSERT INTO #temp
SELECT 2011,
'Q4',
135

INSERT INTO #temp
SELECT 2011,
'Q5',
145

INSERT INTO #temp
SELECT 2012,
'Q6',
15

SELECT *
FROM #temp

DECLARE @sql VARCHAR(max) =''
DECLARE @Qry VARCHAR(max) =''

SELECT @sql = @sql
+ '(select sum(saleamt) from #Temp tt where t.year= tt.year and Qtr = '''
+ qtr + ''') as ''' + qtr + '''' + ','
FROM (SELECT DISTINCT qtr
FROM #temp) t

SET @sql =LEFT(@sql, Len(@sql) - 1)
SET @Qry = 'select Year, ' + @sql
+ ' from #Temp t group by year '

PRINT @Qry

EXECUTE (@Qry)

IF Object_id('tempdb.dbo.#temp')IS NOT NULL
BEGIN
DROP TABLE #temp
END



No comments :

Post a Comment