How to join by column name
- by Daniel Vaca
I have a table T1 such that
gsdv |nsdv |esdv
-------------------
228.90 |216.41|0.00
and a table T2 such that
ds |nm
--------------------------
'Non-Revenue Sales'|'ESDV'
'Gross Sales' |'GSDV'
'Net Sales' |'NSDV'
How do I get the following table?
ds |nm |val
---------------------------------
'Non-Revenue Sales'|'ESDV'|0.00
'Gross Sales' |'GSDV'|228.90
'Net Sales' |'NSDV'|216.41
I know that I can this by doing the following
SELECT ds,nm,esdv val FROM T1,T2 WHERE nm = 'esdv'
UNION
SELECT ds,nm,gsdv val FROM T1,T2 WHERE nm = 'gsdv'
UNION
SELECT ds,nm,nsdv val FROM T1,T2 WHERE nm = 'nsdv'
but I am looking for a more generic/nicer solution. I am using Sybase, but if you can think of a way to do this with other DBMS, please let me know.
Thanks.
