I Have a table with a row containing abc_def_ghi and another table with a row containing 123_456_789.
create table t_info (infoId varchar(255),PKey varchar(255))
insert into t_info values ('ZC','abc_def_ghi')
create table t_value (infoId varchar(255),PKey varchar(255))
insert into t_value values ('ZC','123_456_789')
I need a single select query which displays the output as below
abc def ghi
123 456 789
Kindly help me
What I have tried:
I tried for splitting the values with '_'. But i'm stuck in the making the column name
select
case when CHARINDEX('_',PKey)>0
then
SUBSTRING(PKey,1,CHARINDEX('_',PKey)-1)
else PKey end firstname,
CASE WHEN CHARINDEX('_',PKey)>0
THEN SUBSTRING(PKey,CHARINDEX('_',PKey)+1,len(PKey))
ELSE NULL END as lastname
from t_value where infoId='ZC'