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!!
jfreeman2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
This expert suggested creating a Gigs project.
I think COALESCE can help you out.

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?
ValentinoVBI ConsultantCommented:
ouch, activated "gig suggestion" by accident lol, please ignore that part :)
ste5anSenior DeveloperCommented:
Unpivot the data, reorder it and create a new pivot:

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 ) ,
       ( 23456, '234', NULL, 'ewr', NULL, NULL ) ,
       ( 34567, NULL, 'acc', NULL, NULL, 'def' ) ,
       ( 45678, NULL, NULL, 'jkl', NULL, NULL ) ,
       ( 56789, NULL, NULL, NULL, NULL, 'we1' ) ,
       ( 23450, 'abc', 'acc', 'exy', 'ert', 'def' );

WITH Unpivoted
AS ( SELECT *
     FROM   @TEMP4 T UNPIVOT(CompValue FOR CompType IN(Comp1, Comp2, Comp3, Comp4, Comp5)) U ) ,
     Reordered
AS ( SELECT U.PID ,
            U.CompValue ,
            'Comp' + CAST(ROW_NUMBER() OVER ( PARTITION BY U.PID
                                              ORDER BY U.CompType ASC ) AS VARCHAR(255)) AS NewCompType
     FROM   Unpivoted U )
SELECT *
FROM   Reordered R
    PIVOT (   MIN(CompValue)
              FOR NewCompType IN ( Comp1, Comp2, Comp3, Comp4, Comp5 )) P;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

jfreeman2010Author Commented:
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
ValentinoVBI ConsultantCommented:
See comment by ste5an, it's better :)
jfreeman2010Author Commented:
thanks you very much ste5an.  that is the result what I am looking for !!
Scott PletcherSenior DBACommented:
Oops, nvm.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.