SQL 2016 - Need help reorganizing the output

  • Hello Gurus,

    Looking for some help to manipulate output data.

    Here is my SQL

    WITH SampleData (MAINJOB,JOB1,JOB2,JOB3,JOB4,JOB5,JOB6,JOB7,JOB8,JOB9,JOB10,JOB11) AS

    (

    SELECT 'ABC4013','ABC4014','ABC4067','ABC4099','ABC4098','ABC4060','ABC4071','','','','',''

    UNION ALL

    SELECT 'DEF4061','DEF4062' ,'','','','','','','','','',''

    UNION ALL

    SELECT 'GHI4003','KLM4068','KLM4053','KLM4061','KLM4064','KLM4062','KLM4050','KLM4087','KLM462T','KLM4085','KLM4011','KLM4076'

    )

    SELECT *

    FROM SampleData

    ORDER BY 1

    Current SQL output

    MAINJOB JOB1 JOB2 JOB3 JOB4 JOB5 JOB6 JOB7 JOB8 JOB9 JOB10 JOB11

    ABC4013 ABC4014 ABC4067 ABC4099 ABC4098 ABC4060 ABC4071

    DEF4061 DEF4062

    GHI4003 KLM4068 KLM4053 KLM4061 KLM4064 KLM4062 KLM4050 KLM4087 KLM462T KLM4085 KLM4011 KLM4076

    In the desired output, I would like to return two columns only.

    MainJob and MappedJob

    In first record of each MainJob,  we have main job and then in the mapped job main job again.

    Second row for that main job would be, main job and Job1

    Third row, main job and Job2 etc. as shown below.

    Thank you in advance !

    Desired Output

    MAINJOB MAPPEDJOB

    ABC4013 ABC4013

    ABC4013 ABC4014

    ABC4013 ABC4067

    ABC4013 ABC4099

    ABC4013 ABC4098

    ABC4013 ABC4060

    ABC4013 ABC4071

    DEF4061 DEF4062

    DEF4061 DEF4061

    GHI4003 GHI4003

    GHI4003 KLM4068

    GHI4003 KLM4053

    GHI4003 KLM4061

    GHI4003 KLM4064

    GHI4003 KLM4062

    GHI4003 KLM4050

    GHI4003 KLM4087

    GHI4003 KLM462T

    GHI4003 KLM4085

    GHI4003 KLM4011

    GHI4003 KLM4076

  • This is an UNPIVOT - and you could use that operator, but I prefer CROSS APPLY:

     Select sp.MAINJOB
    , m.MappedJob
    From SampleData sp
    Cross Apply (Values (MAINJOB), (JOB1), (JOB2), (JOB3), (JOB4), (JOB5), (JOB6), (JOB7), (JOB8), (JOB9), (JOB10), (JOB11)) As m(MappedJob)
    Where m.MappedJob <> '';

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi Jeffrey,

    Wow, this is crazy awesome.

    Exactly what I was looking for 🙂

    Thank you for saving the day for me !

    Take care and God Bless.

    DS

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply