2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.
Available in SQL Server 2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.
|
1
2 |
SELECT productId,_year,amountFROM Products |
We have this result :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
productId _year amount124 2001 125125 2001 454126 2001 75127 2002 256128 2004 457129 2004 585130 2002 142131 2002 785132 2005 452133 2005 864134 2005 762135 2004 425136 2003 452137 2003 1024138 2003 575 |
Now we are going to use PIVOT to have the years as columns and for a productId, the sum of amounts for 2001 and 2003 :
|
1
2
3
4
5
6
7 |
SELECT * FROM( SELECT
productId,_year,amount FROM
Products)tPIVOT (SUM(amount) FOR
_yearIN ([2001],[2003])) AS
pvt |
So, we will have this result :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
productId 2001 2003124 125 NULL125 454 NULL126 75 NULL127 NULL
NULL128 NULL
NULL129 NULL
NULL130 NULL
NULL131 NULL
NULL132 NULL
NULL133 NULL
NULL134 NULL
NULL135 NULL
NULL136 NULL
452137 NULL
1024138 NULL
575 |
Ok, that’s nice. But if we consider that we don’t know the names of the columns, we have to make our PIVOT dynamic. Look at the following code :
We are first going to build a string that concatenes all years
|
1
2
3
4
5
6
7 |
DECLARE @years VARCHAR(2000)SELECT
@years = STUFF(( SELECT
DISTINCT ‘],[‘
+ ltrim(str(_year)) FROM
Products ORDER
BY ‘],[‘ + ltrim(str(YEAR(_year))) FOR
XML PATH(‘‘) ), 1, 2, ‘‘) + ‘]‘ |
So this string will contain all years needed for our PIVOT query:
|
1 |
[2001],[2002],[2003],[2004],[2005] |
After that, all we have to do is to dynamically create our PIVOT query. Here is the complete query :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 |
DECLARE @query VARCHAR(4000)DECLARE @years VARCHAR(2000)SELECT
@years = STUFF(( SELECT
DISTINCT ‘],[‘
+ ltrim(str(_year)) FROM
Products ORDER
BY ‘],[‘ + ltrim(str(YEAR(_year))) FOR
XML PATH(‘‘) ), 1, 2, ‘‘) + ‘]‘SET @query =‘SELECT * FROM( SELECT productId,_year,amount FROM Products)tPIVOT (SUM(amount) FOR _yearIN (‘+@years+‘)) AS pvt‘EXECUTE
(@query) |
And here is the displayed result :
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
productId 2001 2002 2003 2004 2005124 125 NULL
NULL NULL
NULL125 454 NULL
NULL NULL
NULL126 75 NULL
NULL NULL
NULL127 NULL
256 NULL
NULL NULL128 NULL
NULL NULL
457 NULL129 NULL
NULL NULL
585 NULL130 NULL
142 NULL
NULL NULL131 NULL
785 NULL
NULL NULL132 NULL
NULL NULL
NULL 452133 NULL
NULL NULL
NULL 864134 NULL
NULL NULL
NULL 762135 NULL
NULL NULL
425 NULL136 NULL
NULL 452 NULL
NULL137 NULL
NULL 1024 NULL
NULL138 NULL
NULL 575 NULL
NULL |
Enjoy ;) PS : You might have this error message when you run the query :
Incorrect syntax near ‘PIVOT’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
So, to enable this feature, you have to set the compatibility level of your database to a higher level by using the following stored procedure :
|
1
2
3
4 |
--If you are running SQL 2005 EXEC sp_dbcmptlevel ‘myDatabaseName‘, 90--If you are running SQL 2008 EXEC sp_dbcmptlevel ‘myDatabaseName‘, 100 |
Using SQL Server 2005/2008 Pivot on Unknown Number,布布扣,bubuko.com
Using SQL Server 2005/2008 Pivot on Unknown Number
原文：http://www.cnblogs.com/happy-Chen/p/3623297.html