select key value pairs into a single row. 1

[jcf27]

I need help trying to convert key value pairs into a single row.

For example -- Table A

id code value
1 A 30
1 C 20
1 H 10
2 B 5
2 C 10
and so on...

How can I easily create a select that could convert the key-value pair into a flat view showing only one id as row and as many columns as necessary. Blank/null where no values are present. For example:

id A B C D E F, etc...
values -------------------

Thanks in advance!

J.

 

[dnoeth]

If you know all different codes:

select
id
,max(case code when 'A' then value end) as "A"
,max(case code when 'B' then value end) as "B"
,max(case code when 'C' then value end) as "C"
,max(case code when 'D' then value end) as "D"
...
from tablea
group by id

Dieter

 

[NVSbe]

I tried this in Access. Seemed to work.


SELECT DISTINCT v1, (select v3 from tbltabel1 t2 where v2 = 'A' and t2.v1 = t1.v1 ) AS A, (select v3 from tbltabel1 t2 where v2 = 'B' and t2.v1 = t1.v1 ) AS B, (select v3 from tbltabel1 t2 where v2 = 'C' and t2.v1 = t1.v1 ) AS C (And so on of course)
FROM tbltabel1 AS t1;
--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

And after reading Dnoeth's post, I must admit I forgot to check for duplicate rows... so all "select v3"'s should be "select max(v3)"'s --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.