How to use Pivot in MicroSoft SQL Server

Following a lot of questions on the internet, how to convert rows of a table to depict as columns of a table and also on how to use a PIVOT in SQL Server – here is an attempt to showcase the same on MS SQL Server 2012

Read more: How to edit top 200 rows in SQL

So let us start with a scenario which is simple enough for the basic understanding.

How to use Pivot in Microsoft SQL Server

Consider a table that stores User Vs Privilege mapping, where we query for a particular USER and get to identify the PRIVILEGEs that he has, here’s the CREATE TABLE script for the same.

CREATE TABLE USER_PRIVILEGES(
  USER_PRIVILEGE_ID INT IDENTITY(1,1),
  USERNAME VARCHAR(25),
    PRIVILEGE VARCHAR(25)
   PRIMARY KEY(USER_PRIVILEGE_ID)
);

 Considering the above, let’s fill it up with some data to work on the example under discussion. The INSERT scripts are as follows, if you want to use them instead of writing them all by yourself:

INSERT INTO USER_PRIVILEGES VALUES('Tim Cook', 'Read')
INSERT INTO USER_PRIVILEGES VALUES('Satya Nadella', 'Read')
INSERT INTO USER_PRIVILEGES VALUES('Satya Nadella', 'Write')
INSERT INTO USER_PRIVILEGES VALUES('Mark Zuckerberg', 'Read')
INSERT INTO USER_PRIVILEGES VALUES('Mark Zuckerberg', 'Write')
INSERT INTO USER_PRIVILEGES VALUES('Mark Zuckerberg', 'Read/Write')
INSERT INTO USER_PRIVILEGES VALUES('Warren Buffet', 'Read')
INSERT INTO USER_PRIVILEGES VALUES('Warren Buffet', 'Write')
INSERT INTO USER_PRIVILEGES VALUES('Warren Buffet', 'Read/Write')
INSERT INTO USER_PRIVILEGES VALUES('Warren Buffet', 'Administrator')
INSERT INTO USER_PRIVILEGES VALUES('Steve Jobs', 'Read')
INSERT INTO USER_PRIVILEGES VALUES('Steve Jobs', 'Write')
INSERT INTO USER_PRIVILEGES VALUES('Steve Jobs', 'Read/Write')
INSERT INTO USER_PRIVILEGES VALUES('Steve Jobs', 'Administrator')
INSERT INTO USER_PRIVILEGES VALUES('Steve Jobs', 'SuperUser')

Now if we do a SELECT, we will be able to see the flat data as follows:

This is a lot of data that can get growing on a day to day basis, and could be hard to get to know the number of Users who have the particular PRIVILEGE which you are interested in and having said that, on a day-to-day basis the number of PRIVILEGES that could be offered to the users can also grow.

This simple PL/SQL should do the trick, to convert your ROWS to COLUMNS and represent the same data in a total different view for your to refer.

DECLARE @columns AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @columns = STUFF((SELECT DISTINCT ',[' + privilege +']'
                         FROM user_privileges 
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1, 1, '')

SET @query = 'SELECT username,' + @columns + ' FROM 
              (
                SELECT username, privilege, 1 as xCount FROM user_privileges
              ) x
              PIVOT 
              (
                count(xCount)
                FOR privilege IN (' + @columns + ')
              ) p
    ORDER BY username DESC'

EXECUTE(@query);

 On executing the above piece of PL/SQL code, you’ll be able to see that data has been reformatted to the following:

Hope that you are able to benefit from this post.Keep checking for more and more fun to do activities, concepts and technical queries on this space.