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.
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