MSSQL 2008 Hosting :: How to Make Pivot Query in SQL Server 2008?

Peter Cole | August 25, 2016 | SQL 2008 Hosting | No Comments

In this tutorial, I am explaining about Pivot table. A pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary’s structure by dragging and dropping fields graphically. This “rotation” or pivoting of the summary table gives the concept its name.

CREATE TABLE #REVENUE
(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[MONTH] VARCHAR(8) NOT NULL,
SALES DECIMAL(8,2) NOT NULL,
EmpId int
)

INSERT INTO #REVENUE
([MONTH],SALES,EmpId)
VALUES
('JAN-2015', 200000.16,1),
('FEB-2015', 220000.17,1),
('MAR-2015', 227000.55,2),
('APR-2015', 247032.75,1),
('MAY-2015', 287652.75,2),
('JUN-2015', 265756.75,2),
('JUN-2016', 265.75,3)
select * from #REVENUE

truncate table #Revenue
---------------Simple Pivot example-------
SELECT * FROM
(SELECT
[MONTH],
SALES,EmpId
FROM #REVENUE)X
PIVOT
(
AVG(SALES)
for [MONTH] in ([JAN-2015],[FEB-2015],[MAR-2015],[APR-2015],[MAY-2015])
)p

-------------Dynamic pivot table----
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME([MONTH])
FROM #REVENUE
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print @cols
SELECT @query =
'SELECT * FROM
(SELECT
[MONTH],
SALES,EmpId
FROM #REVENUE )X
PIVOT
(
AVG(SALES)
for [MONTH] in (' + @cols + ')
) P'

EXEC SP_EXECUTESQL @query

CREATE TABLE #REVENUE

(

ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

[MONTH] VARCHAR(8) NOT NULL,

SALES DECIMAL(8,2) NOT NULL,

EmpId int

)

INSERT INTO #REVENUE

([MONTH],SALES,EmpId)

VALUES

('JAN-2015', 200000.16,1),

('FEB-2015', 220000.17,1),

('MAR-2015', 227000.55,2),

('APR-2015', 247032.75,1),

('MAY-2015', 287652.75,2),

('JUN-2015', 265756.75,2),

('JUN-2016', 265.75,3)

select * from #REVENUE

truncate table #Revenue

---------------Simple Pivot example-------

SELECT * FROM

(SELECT

[MONTH],

SALES,EmpId

FROM #REVENUE)X

PIVOT

(

AVG(SALES)

for [MONTH] in ([JAN-2015],[FEB-2015],[MAR-2015],[APR-2015],[MAY-2015])

-------------Dynamic pivot table----

DECLARE @cols AS NVARCHAR(MAX),

@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME([MONTH])

FROM #REVENUE

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

print @cols

SELECT @query =

'SELECT * FROM

(SELECT

[MONTH],

SALES,EmpId

FROM #REVENUE )X

PIVOT

(

AVG(SALES)

for [MONTH] in (' + @cols + ')

) P'

EXEC SP_EXECUTESQL @query

Related Posts

About The Author

Pete