SQL Server – PIVOT Data With TSQL

Note: Although I wrote this in year 2012, it is still valid. If you are looking to convert rows to a CSV string and vice versa in Oracle, please read my related post on that here. If you are a serious PIVOT’er, I know that you would use Excel or Power Pivot or Power BI. ūüėČ This post is for the technical person seeking to do this in code.

Excel users who work with data have long had the power of PIVOT’ing data that the database folks envied. It used be that, we had to write complex queries to essentially turn rows into columns.

While the below example illustrates the PIVOT feature available since SQL Server 2005, the principle is the same for Oracle Oracle 11g  and forward (the concept remains the same but there are some syntactic nuances to be aware of).

Object Counts By Schema & Object Type (without PIVOT)

Let us say we want to look at all the schemas and the distribution of objects across schemas to get a rough sense

SELECT SCHEMA_NAME(schema_id) AS schema_nm, type_desc, COUNT(1) cnt
FROM sys.objects
GROUP BY SCHEMA_NAME(schema_id), type_desc
ORDER BY SCHEMA_NAME(schema_id), type_desc

I used a database where I have a bunch of Schemas (first column) having different Object types (second column) with their grouped Count (third column)

schema_nm type_desc cnt
Alert CHECK_CONSTRAINT 2
Alert FOREIGN_KEY_CONSTRAINT 2
Alert PRIMARY_KEY_CONSTRAINT 3
Alert USER_TABLE 3
Audit USER_TABLE 1
AuditLog CHECK_CONSTRAINT 2
AuditLog PRIMARY_KEY_CONSTRAINT 1
AuditLog USER_TABLE 1
Core CHECK_CONSTRAINT 9
Core DEFAULT_CONSTRAINT 9
Core FOREIGN_KEY_CONSTRAINT 28
Core PRIMARY_KEY_CONSTRAINT 24
Core UNIQUE_CONSTRAINT 1
Core USER_TABLE 24
Core VIEW 3
Credential CHECK_CONSTRAINT 2
Credential FOREIGN_KEY_CONSTRAINT 10
Credential PRIMARY_KEY_CONSTRAINT 4
Credential USER_TABLE 4
Credential VIEW 2
dbo PRIMARY_KEY_CONSTRAINT 1
dbo SERVICE_QUEUE 3
dbo SQL_SCALAR_FUNCTION 1
dbo SQL_STORED_PROCEDURE 7
dbo UNIQUE_CONSTRAINT 1
dbo USER_TABLE 4
Error USER_TABLE 2
Metric FOREIGN_KEY_CONSTRAINT 7
Metric PRIMARY_KEY_CONSTRAINT 4
Metric USER_TABLE 4
Probe CHECK_CONSTRAINT 4
Probe DEFAULT_CONSTRAINT 31
Probe FOREIGN_KEY_CONSTRAINT 15
Probe PRIMARY_KEY_CONSTRAINT 15
Probe SEQUENCE_OBJECT 1
Probe USER_TABLE 15
Probe VIEW 1
ProbeRun FOREIGN_KEY_CONSTRAINT 16
ProbeRun PRIMARY_KEY_CONSTRAINT 7
ProbeRun USER_TABLE 7
ProbeRun VIEW 1
Props USER_TABLE 4
Role FOREIGN_KEY_CONSTRAINT 16
Role PRIMARY_KEY_CONSTRAINT 11
Role USER_TABLE 11
Scratch USER_TABLE 15
Tag CHECK_CONSTRAINT 7
Tag FOREIGN_KEY_CONSTRAINT 29
Tag PRIMARY_KEY_CONSTRAINT 9
Tag USER_TABLE 9

It is a lot to take in and it is cluttered but basically, I only have a few types of objects in each schema.

SELECT type_desc, COUNT(1) cnt
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) NOT IN ('sys')
GROUP BY type_desc
type_desc cnt
VIEW 7
DEFAULT_CONSTRAINT 40
SQL_STORED_PROCEDURE 7
FOREIGN_KEY_CONSTRAINT 123
SERVICE_QUEUE 3
SEQUENCE_OBJECT 1
CHECK_CONSTRAINT 26
USER_TABLE 104
PRIMARY_KEY_CONSTRAINT 79
SQL_SCALAR_FUNCTION 1
UNIQUE_CONSTRAINT 2

The information is there but it not very pleasant to read. There is a reason business users always convert everything into a PIVOT first thing in Excel before they talk to you about the data!

Object Counts By Schema & Object Type (with PIVOT)

Now, let us PIVOT and try the results (pivoted by type_desc)

SELECT *
FROM
(
       SELECT SCHEMA_NAME(schema_id) AS schema_nm, type_desc
       FROM sys.objects
	   WHERE SCHEMA_NAME(schema_id) NOT IN ('sys')
) AS o
PIVOT (COUNT(type_desc) FOR type_desc in
			(
				[VIEW],
				[DEFAULT_CONSTRAINT],
				[SQL_STORED_PROCEDURE],
				[FOREIGN_KEY_CONSTRAINT],
				[SERVICE_QUEUE],
				[SEQUENCE_OBJECT],
				[CHECK_CONSTRAINT],
				[USER_TABLE],
				[PRIMARY_KEY_CONSTRAINT],
				[SQL_SCALAR_FUNCTION],
				[UNIQUE_CONSTRAINT]
			)
	) a
ORDER BY
	schema_nm

The results are so much easier to look at and comprehend, aren’t they? All object types for a schema are on a single line and it is easy for us to do impact analysis visually.

Note: Please scroll right to view the data.

schema_nm VIEW DEFAULT_CONSTRAINT SQL_STORED_PROCEDURE FOREIGN_KEY_CONSTRAINT SERVICE_QUEUE SEQUENCE_OBJECT CHECK_CONSTRAINT USER_TABLE PRIMARY_KEY_CONSTRAINT SQL_SCALAR_FUNCTION UNIQUE_CONSTRAINT
Alert 0 0 0 2 0 0 2 3 3 0 0
Audit 0 0 0 0 0 0 0 1 0 0 0
AuditLog 0 0 0 0 0 0 2 1 1 0 0
Core 3 9 0 28 0 0 9 24 24 0 1
Credential 2 0 0 10 0 0 2 4 4 0 0
dbo 0 0 7 0 3 0 0 4 1 1 1
Error 0 0 0 0 0 0 0 2 0 0 0
Metric 0 0 0 7 0 0 0 4 4 0 0
Probe 1 31 0 15 0 1 4 15 15 0 0
ProbeRun 1 0 0 16 0 0 0 7 7 0 0
Props 0 0 0 0 0 0 0 4 0 0 0
Role 0 0 0 16 0 0 0 11 11 0 0
Scratch 0 0 0 0 0 0 0 15 0 0 0
Tag 0 0 0 29 0 0 7 9 9 0 0

I could have limited my criteria to just views and tables

SELECT *
FROM
(
       SELECT SCHEMA_NAME(schema_id) AS schema_nm, type_desc
       FROM sys.objects
	   WHERE SCHEMA_NAME(schema_id) NOT IN ('sys')
) AS o
PIVOT (COUNT(type_desc) FOR type_desc in
			(
				[VIEW],
				[USER_TABLE],
			)
	) a
ORDER BY
	schema_nm

And, they are even simpler to view now (schema names and various object types for the schema on a single line)

schema_nm VIEW USER_TABLE
Alert 0 3
Audit 0 1
AuditLog 0 1
Core 3 24
Credential 2 4
dbo 0 4
Error 0 2
Metric 0 4
Probe 1 15
ProbeRun 1 7
Props 0 4
Role 0 11
Scratch 0 15
Tag 0 9

Conclusion:

The syntax is one of those things that you have to get used to after using it for a while. As you keep using it, you will remember but it will take quite a few attempts! If not, you can always refer to this example.

At a later date, we will look at how we can un-pivot.

Advertisements

2 thoughts on “SQL Server – PIVOT Data With TSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s