Need help on t-sql 2012
I have the following data in a temp table:
declare @TEMP4 table (PID int, Comp1 varchar(10), Comp2 varchar(10), Comp3 varchar(10), Comp4 varchar(10), Comp5 varchar(10));
insert into @TEMP4 values(12345, NULL, NULL, '123', NULL, NULL);
insert into @TEMP4 values(23456, '234', NULL, 'ewr', NULL, NULL);
insert into @TEMP4 values(34567, NULL, 'acc', NULL, NULL, 'def');
insert into @TEMP4 values(45678, NULL, NULL, 'jkl', NULL, NULL);
insert into @TEMP4 values(56789, NULL, NULL, NULL, NULL, 'we1');
insert into @TEMP4 values(23450, 'abc', 'acc', 'exy', 'ert', 'def');
would like to have result as follow:
PID comp1 comp2 comp3 comp4 comp5
12345 123 NULL NULL NULL NULL
23456 234 ewr NULL NULL NULL
34567 def NULL NULL NULL NULL
45678 jkl NULL NULL NULL NULL
56789 we1 NULL NULL NULL NULL
23450 abc acc exy ert def
Thank you for helping!!
declare @TEMP4 table (PID int, Comp1 varchar(10), Comp2 varchar(10), Comp3 varchar(10), Comp4 varchar(10), Comp5 varchar(10));
insert into @TEMP4 values(12345, NULL, NULL, '123', NULL, NULL);
insert into @TEMP4 values(23456, '234', NULL, 'ewr', NULL, NULL);
insert into @TEMP4 values(34567, NULL, 'acc', NULL, NULL, 'def');
insert into @TEMP4 values(45678, NULL, NULL, 'jkl', NULL, NULL);
insert into @TEMP4 values(56789, NULL, NULL, NULL, NULL, 'we1');
insert into @TEMP4 values(23450, 'abc', 'acc', 'exy', 'ert', 'def');
would like to have result as follow:
PID comp1 comp2 comp3 comp4 comp5
12345 123 NULL NULL NULL NULL
23456 234 ewr NULL NULL NULL
34567 def NULL NULL NULL NULL
45678 jkl NULL NULL NULL NULL
56789 we1 NULL NULL NULL NULL
23450 abc acc exy ert def
Thank you for helping!!
ouch, activated "gig suggestion" by accident lol, please ignore that part :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ValentinoV - thank you very much for you help.
your suggestion come up close:
select PID,
Comp1 = COALESCE(Comp1, Comp2, Comp3, Comp4, Comp5),
comp2 = COALESCE(Comp2, Comp3, Comp4, Comp5),
comp3 = COALESCE(Comp3, Comp4, Comp5),
comp4 = COALESCE(Comp4, Comp5),
comp5 = Comp5
from @TEMP4
PID Comp1 Comp2 Comp3 Comp4 Comp5
12345 123 123 123 NULL NULL
23456 234 ewr ewr NULL NULL
34567 acc acc def def def
45678 jkl jkl jkl NULL NULL
56789 we1 we1 we1 we1 we1
23450 abc acc exy ert def
your suggestion come up close:
select PID,
Comp1 = COALESCE(Comp1, Comp2, Comp3, Comp4, Comp5),
comp2 = COALESCE(Comp2, Comp3, Comp4, Comp5),
comp3 = COALESCE(Comp3, Comp4, Comp5),
comp4 = COALESCE(Comp4, Comp5),
comp5 = Comp5
from @TEMP4
PID Comp1 Comp2 Comp3 Comp4 Comp5
12345 123 123 123 NULL NULL
23456 234 ewr ewr NULL NULL
34567 acc acc def def def
45678 jkl jkl jkl NULL NULL
56789 we1 we1 we1 we1 we1
23450 abc acc exy ert def
See comment by ste5an, it's better :)
ASKER
thanks you very much ste5an. that is the result what I am looking for !!
Oops, nvm.
For comp1: COALESCE(Comp1, Comp2, Comp3, Comp4, Comp5)
For comp2: COALESCE(Comp2, Comp3, Comp4, Comp5)
For comp3: COALESCE(Comp3, Comp4, Comp5)
and so on...
BTW: what happened with 'acc' in the third record?