Dynamic PIVOT - SQL Server
In SQL Server, Category wise SET statements as given below. We have to prepare one report as per the following scenario
Scenario
How many SET statements are there in each CATEGORY with Alphabet wise ?
1. Create a Table:
CREATE TABLE SQL_SET_Statements
(ID INT IDENTITY(1,1),
Category VARCHAR(MAX),
Statements VARCHAR(MAX))
GO
2. Insert sample records:
INSERT SQL_SET_Statements(Category,Statements) VALUES('Date and time statements','DATEFIRST')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Date and time statements','DATEFORMAT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Locking statements','DEADLOCK_PRIORITY')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Locking statements','LOCK_TIMEOUT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','CONCAT_NULL_YIELDS_NULL')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','CURSOR_CLOSE_ON_COMMIT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','FIPS_FLAGGER')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','IDENTITY_INSERT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','LANGUAGE')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','OFFSETS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Miscellaneous statements','QUOTED_IDENTIFIER')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','ARITHABORT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','ARITHIGNORE')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','FMTONLY')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','NOCOUNT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','NOEXEC')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','NUMERIC_ROUNDABORT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','PARSEONLY')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','QUERY_GOVERNOR_COST_LIMIT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','ROWCOUNT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Query Execution Statements','TEXTSIZE')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_DEFAULTS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_NULL_DFLT_OFF')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_NULL_DFLT_ON')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_NULLS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_PADDING')
INSERT SQL_SET_Statements(Category,Statements) VALUES('SQL-92 Settings statements','ANSI_WARNINGS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','FORCEPLAN')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','SHOWPLAN_ALL')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','SHOWPLAN_TEXT')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','SHOWPLAN_XML')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','STATISTICS IO')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','STATISTICS XML')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','STATISTICS PROFILE')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Statistics statements','STATISTICS TIME')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Transactions statements','IMPLICIT_TRANSACTIONS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Transactions statements','REMOTE_PROC_TRANSACTIONS')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Transactions statements','TRANSACTION ISOLATION LEVEL')
INSERT SQL_SET_Statements(Category,Statements) VALUES('Transactions statements','XACT_ABORT')
GO
3. Dynamic PIVOT Script
DECLARE @Groups VARCHAR(MAX)
DECLARE @PIVOTS NVARCHAR(MAX)
SELECT @Groups = COALESCE(@Groups + ',','') + LEFT(Statements,1) FROM SQL_SET_Statements GROUP BY LEFT(Statements,1)
SELECT @PIVOTS = N'SELECT * FROM (SELECT Category,LEFT(Statements,1) ''Statements'' FROM SQL_SET_Statements) as P
PIVOT
(
COUNT(Statements) FOR Statements IN(' + @Groups + N')
) AS PIVOTS'
EXEC SP_EXECUTESQL @PIVOTS
Cheers
 Attachments
|
| Author: Prashant Mishra 17 Sep 2009 | Member Level: Silver Points : 1 |
Yes Boss! tried to perform a good job, but Dynamic PIVOT Script not working SQL Server2005:)
|