Reordering table data

[Davmaster]

I have a table that has 3 columns: ID, Type, and Value.

I'm looking for a way to rearrange/view the data in a matrix form where ID is the first index and Type is the second index, and Value is the value for that ID/Type.

i.e., I have:
ID Type Value
--------------
ID1 Type1 x
ID2 Type1 y
ID1 Type2 z
ID2 Type2 v

and I want to see it as:

Type1 Type2
--------------------
ID1 | x z
ID2 | y v

How would I write a query to do this? Or is it even possible?

Thanks much!

 

[PHV]

Check your RDBMS manual for CrossTable query or Pivot table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fluteplr]

assuming value is numeric

select id,
sum(case when type = 'type1' then value else 0) as type1,
sum(case when type = 'type2' then value else 0) as type2
group by id
order by id

if it is not numeric replace sum with max, matter in this zero with the empty value equivelant for that data type.