pivot table without aggregation?

Hello, I have a resultset as follows:

fields: Name, RankID

values:
Prorduct A, 4
Product B, 33
Product C, 221
(etc)

Name is always unique. RankID may not be.

I want to take that result set and basically pivot it to have the Name
values as columns, and the RankID of each one as the data. So you
would end up with only one row like:

Product A | Product B | Product C | etc
4 | 33 | 221 | etc

Is this possible? I do not want to sum the data or anything, simply
rotate it sort of.

Any advice is appreciated.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/inde...articleid=15608
http://www.sqlmag.com/articles/inde...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|||Plamen Ratchev wrote:

Quote:

Originally Posted by

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

I think SQL Server 2005 has a built-in pivoting function, but I've
never used it so I don't know the syntax.|||"Ed Murphy" <emurphy42@.socal.rr.comwrote in message
news:460c63e2$0$24701$4c368faf@.roadrunner.com...

Quote:

Originally Posted by

>
I think SQL Server 2005 has a built-in pivoting function, but I've
never used it so I don't know the syntax.

Here is how it can be done with PIVOT in SQL Server 2005:

SELECT [Product A],
[Product B],
[Product C]
FROM Products
PIVOT (SUM(RankID) FOR [Name] IN ([Product A],
[Product B],
[Product C])) AS P

I find the PIVOT syntax to be not so intuitive (unlike UNPIVOT which is
easier to use and understand).

Plamen Ratchev
http://www.SQLStudio.com

Pivot table with no numeric aggregation?

I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.

For example, my initial table could be represented by this structure:

ID

Label

Value

1

a

val1

1

b

val2

1

c

val3

1

d

val4

1

e

val5

2

a

val6

2

b

val7

2

c

val8

2

d

val9

2

e

val10

and I am trying to get it into the following structure:

ID

Label a

Label b

Label c

Label d

Label e

1

val1

val2

val3

val4

val5

2

val6

val7

val8

val9

val10

The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?

Thanks for any replies on this,

Eric

In SQL Server 2005, you can use the PIVOT operator like:

select pt.ID, pt. as [Label a], pt. as [Label b]...

from tbl as t

pivot (min(t.value) for t.Label in ( , , ....)) as pt

In older versions, you can do below:

select t.ID

, min(case t.Label when 'a' then value end) as [Label a]

, min(case t.Label when 'b' then value end) as [Label b]

...

from tbl as t

group by t.ID

|||Thanks, works perfect! I had no idea you could use the min function on varchar.