I would like to populate a table based on a given table:
Given table t1:
id1 (string), id2 (string), value (float)
tyb uanwe_A 6963
tyb uanwe_B 979
tyb uanwe_C 931
I need :
id1, id2, vA, vB, vC
tyb uanwe 6963 979 931
My SQL server query:
select case substring(id2, 6, 1)
when 'A' then [value]
end as [vA]
from t1
but, this does not work for me because I got many "null" in the case that substring(id2, 6, 1) is not 'A'.
select case substring(id2, 6, 1)
when 'A' then [value] end as [vA]
when 'B' then [value] end as [vB]
when 'C' then [value] end as [vC]
end
from t1
I GOT ERROR:
Incorrect syntax near the keyword 'when'.
Any help would be appreciated.