Introduction
In a previous article, I talked about passing an arbitrary number of values in to SQL Server. But, what if you want to get an arbitrary number of values OUT, represented in a clean data set ? That's what we're looking at today.
The data
If you don't have AdventureWorks installed, go and install it now. You can google for a current link, just make sure you install the one for your SQL Server version. I like using this database because it means you have a rich set of data to work against, without having to install much of anything.
A flat solution
We're going to look at the salesorderdetail table, and we're going to get the sum of prices by month and year. To get this out in normal SQL, looks like this:
select year(modifieddate) as year, month(modifieddate) as month, sum(UnitPrice) as unitprice
from sales.SalesOrderDetail
group by year (modifieddate), month(modifieddate)
order by year(modifieddate), month(modifieddate)
This is perfectly workable a lot of the time. However, we want a format where each row is one year, and the months go across the row, one column for each.
PIVOT
The first thing we're going to look at, is the PIVOT key word. This is used to take a set of data and flip it by 90 degrees, if you will, so that things that were individual rows, become individual columns in the data. This is really the main way of doing this, everything else is alternatives that are usually not as good.
Here is the pivot code:
select [year], isnull([1], 0) as 'Jan', isnull([2], 0) as 'Feb', isnull([3], 0) as 'Mar', isnull([4], 0) as 'Apr', isnull([5], 0) as 'May', isnull([6], 0) as 'Jun', isnull([7], 0) as 'Jul', isnull([8], 0) as 'Aug', isnull([9], 0) as 'Sep', isnull([10], 0) as 'Oct', isnull([11], 0) as 'Nov', isnull([12], 0) as 'Dec'
from
(
select UnitPrice as unitprice, year(modifieddate) as year, month(modifieddate) as month from sales.SalesOrderDetail
) as src
pivot
(
sum(unitprice) for month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as pvt
order by [year];
First, run it. You'll see this is much nicer, especially for things like comparing the same month for different years, etc. The syntax can look confusing at first, so let's work through it.
The core of it is this statement:
select UnitPrice as unitprice, year(modifieddate) as year, month(modifieddate) as month from sales.SalesOrderDetail
which returns data like this:
unitprice year month
461.694 2008 6
600.2625 2006 9
36.83 2007 8
3399.99 2005 9
31.584 2007 9
32.9945 2006 9
3374.99 2006 5
21.98 2007 10
2024.994 2005 8
40.5942 2007 7
818.70 2006 2
....
but with duplicates ( because of the same item being ordered more than once in the month.
The main select looks like this:
select [year], isnull([1], 0) as 'Jan', isnull([2], 0) as 'Feb', isnull([3], 0) as 'Mar', isnull([4], 0) as 'Apr', isnull([5], 0) as 'May', isnull([6], 0) as 'Jun', isnull([7], 0) as 'Jul', isnull([8], 0) as 'Aug', isnull([9], 0) as 'Sep', isnull([10], 0) as 'Oct', isnull([11], 0) as 'Nov', isnull([12], 0) as 'Dec'
The IsNull statements just turn null values in to 0s, to make it more readable. You can remove them if you want to show 'I had no data' as being separate to 'the data was 0'. We are then reading columns named from 1 to 12, and then renaming them to the name of the month in question. But, we don't select those values at all. They come from the pivot statement, which is this:
pivot
(
sum(unitprice) for month in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) as pvt
So what the pivot does, is take the data from the core statement, and choose what to pivot on. In this case, it's going to pivot on the month value, and create columns with values from 1-12. Note - you HAVE to know your full list of end values, you CANNOT do this:
pivot
(
sum(unitprice) for month in (select id from months)
) as pvt
More on this later, but this is the main weakness with all the approaches I am going to show you before the last one.
So, the year value is not listed as a pivot value, or as an aggregate value, so it is just passed through. The month becomes the window for the sum operation, in that sense, it's doing the same things as our initial statement. What is nice is, the year IS being considered, if it was not, the values for all our rows would be the same. So, any rows you add, create the window, not just the one you pivot on.
So, a pivot involves the following:
- a core statement to get the data you want
- a pivot statement that defines the value to return against the pivot, the value to pivot on, and the range of possible values
- a select statement that selects the combination of data from the core statement, and the pivoted values that you want.
It's really not that hard, but it takes some getting used to. I recommend playing with it for a while, coming up with some ideas for ways to pivot data, and then working out how to do them. One suggestion - what if you wanted to pivot this example, but with quarterly data ?
Using Case
You can reproduce what a pivot does by writing your own case statements to create the columns and get them to aggregate accordingly. It's pretty straightforward, here's the example:
SELECT YEAR(modifieddate) AS OrderYear,
SUM(CASE WHEN DATEPART(m, modifieddate) = 1
THEN unitPrice ELSE 0 END) AS 'Jan',
SUM(CASE WHEN DATEPART(m, modifieddate) = 2
THEN unitPrice ELSE 0 END) AS 'Feb',
SUM(CASE WHEN DATEPART(m, modifieddate) = 3
THEN unitPrice ELSE 0 END) AS 'Mar',
SUM(CASE WHEN DATEPART(m, modifieddate) = 4
THEN unitPrice ELSE 0 END) AS 'Apr',
SUM(CASE WHEN DATEPART(m, modifieddate) = 5
THEN unitPrice ELSE 0 END) AS 'May',
SUM(CASE WHEN DATEPART(m, modifieddate) = 6
THEN unitPrice ELSE 0 END) AS 'Jun',
SUM(CASE WHEN DATEPART(m, modifieddate) = 7
THEN unitPrice ELSE 0 END) AS 'Jul',
SUM(CASE WHEN DATEPART(m, modifieddate) = 8
THEN unitPrice ELSE 0 END) AS 'Aug',
SUM(CASE WHEN DATEPART(m, modifieddate) = 9
THEN unitPrice ELSE 0 END) AS 'Sep',
SUM(CASE WHEN DATEPART(m, modifieddate) = 10
THEN unitPrice ELSE 0 END) AS 'Oct',
SUM(CASE WHEN DATEPART(m, modifieddate) = 11
THEN unitPrice ELSE 0 END) AS 'Nov',
SUM(CASE WHEN DATEPART(m, modifieddate) = 12
THEN unitPrice ELSE 0 END) AS 'Dec'
FROM sales.SalesOrderDetail
GROUP BY DATEPART(yyyy, modifieddate)
order by orderyear;
So we use a sum statement for each column, and inside that we put a case statement to only aggregate values for that month, and we use a group by to define the window for each sum statement. Note that because of the order of operations in a SQL query, the column does not exist yet when our GROUP BY occurs, so I cannot GROUP BY ORDERYEAR, I have to use the datepart statement again. Note also that year(modifieddate) and datepart(yyyy, modifieddate) are the same thing.
The David Rozenshtein method
This is a method I stumbled across while searching to make sure this article was as complete as possible. David appears to be an author of Transact SQL books, so I assume this method appeared in one of his books. It essentially uses some trickery to do the same as the case statement, but avoid the case statement. Here it is:
SELECT YEAR(modifieddate) AS OrderYear,
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 1)))) AS 'Jan',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 2)))) AS 'Feb',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 3)))) AS 'Mar',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 4)))) AS 'Apr',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 5)))) AS 'May',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 6)))) AS 'Jun',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 7)))) AS 'Jul',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 8)))) AS 'Aug',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 9)))) AS 'Sep',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 10)))) AS 'Oct',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 11)))) AS 'Nov',
SUM(unitPrice * (1 - ABS(SIGN(MONTH(modifieddate) - 12)))) AS 'Dec'
FROM sales.SalesOrderDetail
GROUP BY YEAR(modifieddate)
order by orderyear;
Basically, it uses some trickery around the return date of the MONTH function ( note, the original uses DATEPART, but I thought it was hard enough to read as it was ). The end result is that for Jan, the unitprice is multiplied by 1 if the month is Jan, and by 0 otherwise.
I've put some code in to the SQL file for you to run, like this:
select (1 - ABS(SIGN(2 - 1)))
select (1 - ABS(SIGN(2 - 2)))
select (1 - ABS(SIGN(2 - 3)))
This will return 0, 1, 0. What is it doing ? Sign will return -1 for values < 0, 0 for 0, and 1 for values > 0. So, sign( -12 ) will return -1. sign (12) will return 1. ABS returns the absolute value. This means that the negative sign is stripped. So, abs(sign(xxx)) will return 0 if xxx is zero, otherwise it returns 1. So, unitPrice * (1 - abs(sign(xxx)) means unitPrice * 1 if xxx is 0, and unitPrice * 1 if xxx is not 0. That's basically it. It's a bit 'clever' for
my tastes, I'm not sure ( and I admit I've done no testing ) if SQL Server is faster at doing this sort of RBAR ( row by row ) math, compared to row by row case statements.
Using a matrix table
The idea here is simple. Create a table that creates a lookup table to give you the 1s and 0s that the previous solution was calculating, with the same end effect. Here is the SQL to create the table:
CREATE TABLE MonthMatrix (
month_nbr INT NOT NULL PRIMARY KEY
CHECK (month_nbr BETWEEN 1 AND 12),
jan INT NOT NULL DEFAULT 0
CHECK (jan IN (0, 1)),
feb INT NOT NULL DEFAULT 0
CHECK (feb IN (0, 1)),
mar INT NOT NULL DEFAULT 0
CHECK (mar IN (0, 1)),
apr INT NOT NULL DEFAULT 0
CHECK (apr IN (0, 1)),
may INT NOT NULL DEFAULT 0
CHECK (may IN (0, 1)),
jun INT NOT NULL DEFAULT 0
CHECK (jun IN (0, 1)),
jul INT NOT NULL DEFAULT 0
CHECK (jul IN (0, 1)),
aug INT NOT NULL DEFAULT 0
CHECK (aug IN (0, 1)),
sep INT NOT NULL DEFAULT 0
CHECK (sep IN (0, 1)),
oct INT NOT NULL DEFAULT 0
CHECK (oct IN (0, 1)),
nov INT NOT NULL DEFAULT 0
CHECK (nov IN (0, 1)),
dec INT NOT NULL DEFAULT 0
CHECK (dec IN (0, 1)));
INSERT INTO MonthMatrix (month_nbr, jan) VALUES (1, 1);
INSERT INTO MonthMatrix (month_nbr, feb) VALUES (2, 1);
INSERT INTO MonthMatrix (month_nbr, mar) VALUES (3, 1);
INSERT INTO MonthMatrix (month_nbr, apr) VALUES (4, 1);
INSERT INTO MonthMatrix (month_nbr, may) VALUES (5, 1);
INSERT INTO MonthMatrix (month_nbr, jun) VALUES (6, 1);
INSERT INTO MonthMatrix (month_nbr, jul) VALUES (7, 1);
INSERT INTO MonthMatrix (month_nbr, aug) VALUES (8, 1);
INSERT INTO MonthMatrix (month_nbr, sep) VALUES (9, 1);
INSERT INTO MonthMatrix (month_nbr, oct) VALUES (10, 1);
INSERT INTO MonthMatrix (month_nbr, nov) VALUES (11, 1);
INSERT INTO MonthMatrix (month_nbr, dec) VALUES (12, 1);
And once you have that, you can do this:
SELECT DATEPART(yyyy, modifieddate) AS OrderYear,
SUM(UnitPrice * jan) AS 'Jan',
SUM(UnitPrice * feb) AS 'Feb',
SUM(UnitPrice * mar) AS 'Mar',
SUM(UnitPrice * apr) AS 'Apr',
SUM(UnitPrice * may) AS 'May',
SUM(UnitPrice * jun) AS 'Jun',
SUM(UnitPrice * jul) AS 'Jul',
SUM(UnitPrice * Aug) AS 'Aug',
SUM(UnitPrice * Sep) AS 'Sep',
SUM(UnitPrice * Oct) AS 'Oct',
SUM(UnitPrice * Nov) AS 'Nov',
SUM(UnitPrice * Dec) AS 'Dec'
FROM Sales.SalesOrderDetail AS S
JOIN MonthMatrix AS M
ON DATEPART(m, S.modifieddate) = M.month_nbr
GROUP BY DATEPART(yyyy, modifieddate)
order by orderyear;
Hopefully the way it works is obvious. It's got to be quicker than both of the prior solutions, as it's fully set based.
I can see using it for months and quarters, but I can't see creating a matrix table for each possible set of values I want to PIVOT.
Using outer apply
The cross apply operator calls every row in table1 for every row in table2. As such, it's cost is exponential. This also means the cost of adding columns to pivot on, is exponential. I don't recommend EVER doing this, the samples I found online pivoted on 3 values. This SQL, which pivots on 12, ran for 16 hours before I gave up and stopped it.
SELECT distinct DATEPART(yyyy, modifieddate), D1.[Jan], D2.[Feb], D3.[Mar], D4.[Apr], D5.[May], D6.[Jun], D7.[Jul],
D8.Aug, D9.Sep, D10.Oct, D11.Nov, D12.[Dec], T.Total
FROM Sales.SalesOrderDetail SOD
OUTER APPLY (SELECT DATEPART(yyyy, modifieddate) as [year], SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Jan]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 1) D1
OUTER APPLY (SELECT DATEPART(yyyy, modifieddate) as [year], SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Feb]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 2) D2
OUTER APPLY (SELECT DATEPART(yyyy, modifieddate) as [year], SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Mar]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 3) D3
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Apr]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 4) D4
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [May]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 5) D5
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Jun]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 6) D6
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Jul]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 7) D7
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Aug]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 8) D8
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Sep]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 9) D9
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Oct]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 10) D10
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Nov]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 11) D11
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS [Dec]
from Sales.SalesOrderDetail s1
where month(modifieddate) = 12) D12
OUTER APPLY (SELECT SUM(UnitPrice) over (partition by DATEPART(yyyy, modifieddate)) AS Total
FROM Sales.SalesOrderDetail S
WHERE year(S.modifieddate) = year(sod.ModifiedDate)
) t
The story so far
So we've looked at several possible ways to do the same thing, with all the other ways really being ways to avoid the pivot keyword, in my opinion for no good reason unless you're stuck with a pre SS2005 database. However, the answer to the question 'what is the best way' is often 'it depends', so I've tried to find a number of alternatives and present them, as I am sure there are times when each of these ( even outer apply ) is the best choice. However, they all suffer from the same basic issue. You need to know ahead of time what your pivot values are. This is fine for time based queries ( there will never be a fifth quarter or a 13th month or a 32nd day of the month ). However, it is a serious liability for other forms of data, such as user names. The way I see this 'solved' in online articles is procs that build a SQL string using a select, then use EXEC to execute that string mashed SQL. I would NEVER do this. I don't see any point in using stored procs, if all they do, is string mash and execute semi arbitrary SQL. But, I admit, if you want one column per value, that is probably the only way to do it, with an arbitrary list of values.
Using FOR XML
However, if you can live with getting your values all scrunched up in one column, then there is a solution. I've seen this solution widely presented on the web in a 'single shot copy and paste' sort of way. I want to walk you through it, so you can understand what it is doing. So, please don't go to the end, bare with me, and run each statement I am giving you in turn, so you can understand what they are doing.
First, please run this:
SELECT distinct month(modifieddate)
FROM sales.SalesOrderDetail s
FOR XML PATH('month')
You get:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
<month>9</month><month>3</month><month>12</month><month>6</month><month>7</month><month>1</month><month>10</month><month>4</month><month>5</month><month>2</month><month>11</month><month>8</month>
Note that your column has a random name and the month ids, in XML nodes called 'month'. This is an XML snippet, it's not valid XML as it has no root
Now run this:
SELECT distinct convert(varchar(2), month(modifieddate))
FROM sales.SalesOrderDetail s
FOR XML PATH('')
You get back something like this:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
312982611171054
Interestingly, the order changes when you remove the node name, but what we see here is, you can ask for XML without specifying a node name, and you just get back the string with no XML nodes.
Now try this:
SELECT distinct ', ' +convert(varchar(2), month(modifieddate))
FROM sales.SalesOrderDetail s
FOR XML PATH('')
This returns:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
, 11, 1, 8, 3, 6, 9, 4, 2, 10, 5, 12, 7
which is not surprising, but clearly we can now see how this is starting to be useful to us.
Now let's try something different. Run this:
select stuff(', 1, 2, 3, 4, 5, 6', 1, 2, '')
The stuff method allows you to specify the start and length of a string to remove, and the string to replace it with.
This seems a little arcane to me, but it suits our purposes. Taking a string in the format we're able to generate, and taking a 2 character string, from the first ordinal, and replacing it with an empty string, gives us this:
1, 2, 3, 4, 5, 6
So if we put these two ideas together, we can run this:
SELECT
distinct year(modifieddate),
STUFF
(
(
SELECT ',' + convert(varchar(2), month(modifieddate))
FROM sales.SalesOrderDetail s
WHERE year(s.modifieddate) = year(so.ModifiedDate)
group BY month(modifieddate)
order by month(modifieddate)
FOR XML PATH('')
), 1, 1, ''
) AS Months
FROM
sales.SalesOrderDetail so order by year(modifieddate)
This gives us a list of months for each year. These are the months for which we have values. Now let's make it useful:
SELECT distinct year(modifieddate),
STUFF
(
(
SELECT ',' + convert(varchar(2), month(modifieddate)) + ': ' + convert(varchar(10), sum(UnitPrice))
FROM sales.SalesOrderDetail s
WHERE year(s.modifieddate) = year(so.ModifiedDate)
group BY month(modifieddate)
order by month(modifieddate)
FOR XML PATH('')
), 1, 1, ''
) AS Months
FROM
sales.SalesOrderDetail so order by year(modifieddate)
This is a LOT slower than doing a PIVOT. The result looks like this:
Year Months
2005 7: 704154.94,8: 1101956.11,9: 913860.60,10: 851378.61,11: 1376044.03,12: 1345397.82
2006 1: 901717.40,2: 1327442.35,3: 1208028.43,4: 1025423.92,5: 1520124.39,6: 1129626.03,7: 1104691.22,8: 1650825.53,9: 1148641.00,10: 952477.60,11: 1302360.94,12: 1282373.57
2007 1: 860448.52,2: 1302379.28,3: 1032358.48,4: 1042209.28,5: 1486359.64,6: 1217570.78,7: 1653869.56,8: 2135760.74,9: 2302144.85,10: 1767180.26,11: 2348285.76,12: 2981398.52
2008 1: 1942234.14,2: 2459396.65,3: 2537894.73,4: 2291712.32,5: 2994454.75,6: 3170724.26,7: 50840.63
Note I had to convert my values in to varchar in order to be able to concatenate them, but I was able to use things like sum in there, and generate exactly the data I wanted. I deliberately did not put a space after the comma, to show that I can change the parameters to STUFF for any length leading string to remove.
In this case, I think PIVOT is both faster, and more convenient. So let's look at a better example. I want, for each order, to get a list of all the products that were in that order. Hopefully having walked through the previous example, you can look at this one and see what it is doing.
select s.SalesOrderId,
stuff
(
(
SELECT ';' + p.Name
from Production.Product p
inner join sales.SalesOrderDetail so on so.ProductId = p.ProductId
where s.salesorderid = so.SalesOrderID
FOR XML PATH('')
), 1, 1, ''
) as Products
from
sales.SalesOrderDetail s group by salesorderid
order by salesorderId
As the product names contain commas, I've used a semicolon as a delimiter. Here is a sample of the output:
| SalesOrderId | Products |
| 43659 | Mountain-100 Black, 42;Mountain-100 Black, 44;Mountain-100 Black, 48;Mountain-100 Silver,
38;Mountain-100 Silver, 42;Mountain-100 Silver, 44;Mountain-100 Silver, 48;Long-Sleeve Logo Jersey, M;Long-Sleeve Logo
Jersey, XL;Mountain Bike Socks, M;AWC Logo Cap;Sport-100 Helmet, Blue |
| 43660 | Road-650 Red, 44;Road-450 Red, 52 |
| 43661 | HL Mountain Frame - Black, 48;HL Mountain Frame - Black, 42;HL Mountain Frame - Black, 38;AWC Logo
Cap;Long-Sleeve Logo Jersey, L;HL Mountain Frame - Silver, 46;Mountain-100 Black, 38;Mountain-100 Black, 48;Sport-100
Helmet, Blue;HL Mountain Frame - Silver, 48;Mountain-100 Black, 42;Mountain-100 Silver, 44;Long-Sleeve Logo Jersey,
XL;Mountain-100 Black, 44;Sport-100 Helmet, Black |
| 43662 | Road-650 Red, 52;Road-650 Black, 52;LL Road Frame - Red, 62;Road-450 Red, 58;LL Road Frame - Red,
44;Road-650 Red, 44;Road-650 Black, 58;Road-650 Black, 44;Road-150 Red, 56;Road-450 Red, 44;Road-650 Red, 48;ML Road Frame
- Red, 48;Road-450 Red, 52;LL Road Frame - Red, 60;LL Road Frame - Black, 58;Road-150 Red, 62;Road-650 Red, 60;LL Road
Frame - Red, 48;ML Road Frame - Red, 52;LL Road Frame - Black, 52;Road-650 Black, 60;Road-450 Red, 60 |
| 43663 | Road-650 Red, 60 |
| 43664 | Mountain-100 Silver, 42;Mountain-100 Black, 38;Long-Sleeve Logo Jersey, M;Long-Sleeve Logo Jersey,
XL;Mountain-100 Black, 44;Mountain-100 Silver, 38;Mountain-100 Silver, 44;Mountain-100 Black, 48 |
| 43665 | Sport-100 Helmet, Blue;Mountain-100 Silver, 44;Sport-100 Helmet, Red;Long-Sleeve Logo Jersey,
L;Mountain-100 Black, 44;AWC Logo Cap;Mountain-100 Black, 38;Mountain-100 Black, 48;Mountain Bike Socks, M;Mountain-100
Black, 42 |
| 43666 | Road-650 Red, 52;Road-150 Red, 56;ML Road Frame - Red, 48;Road-450 Red, 44;Road-650 Black, 44;Road-
650 Black, 60 |
| 43667 | Mountain Bike Socks, L;Mountain-100 Silver, 44;Mountain-100 Black, 48;Mountain-100 Black,
38 |
| 43668 | Road-450 Red, 44;Road-150 Red, 56;Road-650 Red, 60;Road-650 Black, 58;Long-Sleeve Logo Jersey, L;LL
Road Frame - Red, 62;Sport-100 Helmet, Red;Sport-100 Helmet, Blue;Road-450 Red, 58;AWC Logo Cap;LL Road Frame - Red,
60;Road-450 Red, 60;Road-650 Red, 62;Road-650 Black, 52;LL Road Frame - Red, 48;Road-650 Red, 52;Road-650 Black, 60;LL
Road Frame - Red, 44;Long-Sleeve Logo Jersey, XL;Road-650 Black, 44;ML Road Frame - Red, 48;Road-450 Red, 52;Road-650 Red,
44;LL Road Frame - Black, 52;Long-Sleeve Logo Jersey, M;LL Road Frame - Black, 58;Sport-100 Helmet, Black;ML Road Frame -
Red, 52;Road-650 Red, 48 |
| 43669 | HL Mountain Frame - Black, 38 |
| 43670 | Mountain Bike Socks, L;Mountain Bike Socks, M;Mountain-100 Silver, 44;Mountain-100 Black,
42 |
| 43671 | Road-150 Red, 56;Long-Sleeve Logo Jersey, M;Road-450 Red, 44;Road-650 Black, 44;ML Road Frame - Red,
48;Road-650 Red, 48;Road-450 Red, 60;Road-650 Red, 52;Long-Sleeve Logo Jersey, XL;Sport-100 Helmet, Blue;Sport-100 Helmet,
Black |
| 43672 | Mountain Bike Socks, M;Mountain-100 Black, 42;Mountain-100 Silver, 48 |
| 43673 | Road-450 Red, 58;Long-Sleeve Logo Jersey, L;LL Road Frame - Red, 60;Road-650 Red, 62;Road-650 Red,
44;LL Road Frame - Black, 52;Road-650 Black, 52;AWC Logo Cap;Sport-100 Helmet, Black;Sport-100 Helmet, Blue;Sport-100
Helmet, Red |
| 43674 | Road-450 Red, 52 |
Conclusion
Hopefully you've found this article helpful. I know that I was under the impression that pivot was hard to use, and six months ago, I had no idea how easy it is to get an arbitrary list of values back from SQL Server. It would be nice if pivot could use a SELECT to get a list of values to pivot on, even if it internally didn't do much better than the solutions I see online with string mashed SQL. However, the XML based solution is very flexible and very powerful, if you don't mind having all your data in the one column.
As always, I encourage any questions about SQL, and especially about the things discussed in this article. If you know something I missed, speak up so I can improve the article. I'll be on the forums looking for SQL questions ( although most seem to get answered while I sleep ), and working on another installment for next week.....
