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,amount
FROM 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
_year
IN ([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 NULL
125 454 NULL
126 75 NULL
127 NULL
NULL
128 NULL
NULL
129 NULL
NULL
130 NULL
NULL
131 NULL
NULL
132 NULL
NULL
133 NULL
NULL
134 NULL
NULL
135 NULL
NULL
136 NULL
452
137 NULL
1024
138 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
NULL
125 454 NULL
NULL NULL
NULL
126 75 NULL
NULL NULL
NULL
127 NULL
256 NULL
NULL NULL
128 NULL
NULL NULL
457 NULL
129 NULL
NULL NULL
585 NULL
130 NULL
142 NULL
NULL NULL
131 NULL
785 NULL
NULL NULL
132 NULL
NULL NULL
NULL 452
133 NULL
NULL NULL
NULL 864
134 NULL
NULL NULL
NULL 762
135 NULL
NULL NULL
425 NULL
136 NULL
NULL 452 NULL
NULL
137 NULL
NULL 1024 NULL
NULL
138 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