Using PIVOT in SQL Server

By Mike Gledhill

This is another topic where I struggled to find a single decent example.

Here's how you would use PIVOT command to change the format of your SQL Server data.
 

1. Create your sample data

Let's start by creating some sample data which we can play with.

CREATE TABLE tblSomething
(
    [gid] nvarchar(100),
    [uid] nvarchar(100),
    [dat] datetime,
    [origamt] int,
    [disamt] int
)
GO
INSERT INTO tblSomething VALUES ('AA', 'a', '2016-05-12', 200, 210)
INSERT INTO tblSomething VALUES ('AA', 'b', '2016-05-12', 300, 305)
INSERT INTO tblSomething VALUES ('AA', 'c', '2016-05-12', 150, 116)
INSERT INTO tblSomething VALUES ('BB', 'a', '2016-05-12', 120, 125)
INSERT INTO tblSomething VALUES ('BB', 'c', '2016-05-12', 130, 136)
INSERT INTO tblSomething VALUES ('CC', 'a', '2016-05-12', 112, 115)
INSERT INTO tblSomething VALUES ('CC', 'b', '2016-05-12', 135, 136)
GO

Our target is to use PIVOT to turn this data into this table:

(In this example, we'll only actually use 3 out of the 5 fields in our table.)
 

2. Write a SELECT which returns 3 columns

Next, you need to write a SELECT statement which returns exactly three columns.

It can be as complicated or simple as you like, as long as it returns 3 columns.

• The first column will contain the values which will appear in your PIVOT table's left-hand column.
• The second column will contain the list of values which will appear along the top row.
• The values in the third column will be positioned within your PIVOT table, based on the row/column headers.

In our example, this is easily done:

SELECT [gid], [uid], [origamt]
FROM tblSomething

... and this gives us our three columns...

This is an important part, and vital to understanding how the PIVOT works.

It's worth spending a minute comparing the values in these 7 rows to the overall Pivot Table results, shown above.
Notice how the 7 values in the third column ("origamt") get positioned within the pivot table.
 

3. Find a list of distinct values

Now, we need to run some script to get a list of distinct values in the second column from our SELECT statement in part 2.
Why ? Well, have another look at our pivot table results image (shown in part 1).

We specifically need to be able to write a SQL statement starting with this...

SELECT guid, [a], [b], [c]
FROM ....something....

...so that our PIVOT table end result does contain our left-hand column, then our list of distinct middle-column values.

To get the list of distinct values, in a usable form, I'm going to run this script:

DECLARE @LongString nvarchar(4000)

SELECT @LongString = COALESCE(@LongString + ', ', '') + '[' + [uid] + ']'
FROM [tblSomething]
GROUP BY [uid]

SELECT @LongString AS 'Subquery'

When I run that, I get this result...

After running this query, you'll need to cut'n'paste this string value, as we'll need to paste it twice into the final SQL statement shown in step 4.
 

4. Put it all together

This is the tricky bit.

You need to combine your SELECT command from Step 2, plus the results string from Step 3, into a single SELECT command, which uses the PIVOT command to turn it into a pivot table

Here's what your SQL would look like:

SELECT [gid],
    --  This is the "results string" from part 3
    [a], [b], [c]
FROM (
    --  Here's our "SELECT" statement from part 2
    SELECT [gid], [uid], [origamt]
    FROM tblSomething
) tmp ([gid], [uid], [origamt])
pivot (
    MAX([origamt]) for [uid] in (
        --  This is the "results string" from part 3 again
        [a], [b], [c]
    )
) p

... and here's a confusing image, which shows where the components come from, and the results of running this command, and a reminder of the end result.

Obviously, in each of these steps, you'll need to modify the SQL to use your own database field names.

But that, painfully, is how you use PIVOT with your SQL Server data.

It's not pretty, it's not particular readable, but if you follow these steps, you should be able to use PIVOT commands in your own SQL.

Comments

blog comments powered by Disqus