Re: pivot table without aggregation?
- From: "Plamen Ratchev" <Plamen@xxxxxxxxxxxxx>
- Date: Thu, 29 Mar 2007 16:30:35 GMT
Something like this should do it:
SELECT SUM(CASE WHEN [Name] = 'Product A' THEN RankID ELSE 0 END) AS
'Product A',
SUM(CASE WHEN [Name] = 'Product B' THEN RankID ELSE 0 END) AS
'Product B',
SUM(CASE WHEN [Name] = 'Product C' THEN RankID ELSE 0 END) AS
'Product C'
FROM PRODUCTS
Note that since your product name is unique the SUM here is just to pull the
data into one row. You could use MAX too but then if there is negative rank
you may have to handle it differently.
If this list of products is very dynamic, then you should look into dynamic
pivoting. Here are a few resources if you want to look in that direction:
http://www.sqlmag.com/articles/index.cfm?articleid=15608
http://www.sqlmag.com/articles/index.cfm?articleid=94268
http://www.sqlteam.com/item.asp?ItemID=2955
Also, most reporting tools have very good support for pivoting.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
.
- Follow-Ups:
- Re: pivot table without aggregation?
- From: Ed Murphy
- Re: pivot table without aggregation?
- References:
- pivot table without aggregation?
- From: Mike
- pivot table without aggregation?
- Prev by Date: pivot table without aggregation?
- Next by Date: SSIS PAckage Hangs
- Previous by thread: pivot table without aggregation?
- Next by thread: Re: pivot table without aggregation?
- Index(es):
Relevant Pages
|
