January 26, 2021 at 8:26 pm
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
January 26, 2021 at 10:20 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 26, 2021 at 10:30 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply