insert duplicate records for each flag 1

[markbrum]

Hi,

I have a table like this:-

name a b c d
john 0 0 5 0
dave 2 4 0 5
mark 0 9 0 8

i would like one line for each name where the value isn't zero i.e.

name item value
john c 5
dave a 2
dave b 4
dave d 5
mark b 9
mark d 8

could anyone please point me in the right direction?

thanks a lot.

mark.

 

[Remou]

A Union query should suit.

 

[PHV]

Something like this ?
SELECT name, 'a' AS item, a AS value FROM yourTable WHERE a <> 0
UNION SELECT name, 'b', b FROM yourTable WHERE b <> 0
UNION SELECT name, 'c', c FROM yourTable WHERE c <> 0
UNION SELECT name, 'd', d FROM yourTable WHERE d <> 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markbrum]

that worked great, thanks very much.