Dynamic PIVOT - SQL Server

Pandians
Posted by Pandians under Sql Server category on | Views : 5863
In SQL Server, we want to generate 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

Comments or Responses

Login to post response