nested decode function

96 Views

Hi

i need help to write nested decode function in data services..

Please find below same table with the result expected:-

CREATE TABLE #t (id INT,cond_1 varchar(5), cond_2 varchar(5), cond_3 varchar(5),cond_4 varchar(5), cond_5 varchar(5), cond_6 varchar(5))

INSERT INTO #t VALUES (101,'I47','P22','I77','','','')
INSERT INTO #t VALUES (101,'I47','K11','L28','','','')
INSERT INTO #t VALUES (102,'J27','K12','I77','T10','','')
INSERT INTO #t VALUES (102,'L21','K12','I47','T10','','')

select * from #t

CREATE TABLE #t_res (id INT,all_cond varchar(50))

INSERT INTO #t_res VALUES (101,'I47,P22,I77')
INSERT INTO #t_res VALUES (101,'I47,K11,L28')
INSERT INTO #t_res VALUES (102,'J27,K12,I77,T10')
INSERT INTO #t_res VALUES (102,'L21,K12,I47,T10')

select * from #t_res

thanks for help