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