MSSQL 2008 Hosting :: How to Make Pivot Query in SQL Server 2008?
|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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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 |