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.