May 5, 2021 at 4:11 pm
Hello SQL Friends,
Looking for some help again to rearrange output data.
In real example, the data is pulled from two tables into one so AVAILPC and SELECTEDPC columns do not have any common join.
Top results example in the screenshot below shows what this SQL query is returning however the bottom example in the screenshot is what I'm looking for.
Thank you all and stay blessed !
;WITH SampleData ([POLICY],[AVAILPC], [SELECTEDPC]) AS
(
SELECT 'SHIPPING','REG1','NULL'
UNION ALL SELECT 'SHIPPING','REG2','NULL'
UNION ALL SELECT 'SHIPPING','EVE1','NULL'
UNION ALL SELECT 'SHIPPING','EVE2','NULL'
UNION ALL SELECT 'SHIPPING','NULL','SICK1'
UNION ALL SELECT 'SHIPPING','NULL','SICK2'
UNION ALL SELECT 'SHIPPING','NULL','SICK3'
)
SELECT * FROM SampleData;
May 5, 2021 at 4:55 pm
To be absolutely sure of correct matches, you'll need something to sequence the rows so that you know the specific order of the rows. I added an id for that, but it could be datetime or some other value. Ignore it if you don't need to match them in any particular seq -- I couldn't determine which way you wanted from what you've stated so far.
;WITH SampleData (id,[POLICY],[AVAILPC],[SELECTEDPC]) AS
(
SELECT CAST(1 AS int), 'SHIPPING','REG1',NULL
UNION ALL SELECT 2, 'SHIPPING','REG2',NULL
UNION ALL SELECT 3, 'SHIPPING','EVE1',NULL
UNION ALL SELECT 4, 'SHIPPING','EVE2',NULL
UNION ALL SELECT 5, 'SHIPPING',NULL,'SICK1'
UNION ALL SELECT 6, 'SHIPPING',NULL,'SICK2'
UNION ALL SELECT 7, 'SHIPPING',NULL,'SICK3'
)
SELECT
COALESCE(AVL.POLICY, SEL.POLICY) AS POLICY,
AVL.AVAILPC,
SEL.SELECTEDPC
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY POLICY ORDER BY id) AS row_num
FROM SampleData
WHERE AVAILPC IS NOT NULL
) AS AVL
FULL OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY POLICY ORDER BY id) AS row_num
FROM SampleData
WHERE SELECTEDPC IS NOT NULL
) AS SEL ON SEL.POLICY = AVL.POLICY AND SEL.row_num = AVL.row_num
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 5, 2021 at 4:59 pm
How are you mapping "REG1" to "SICK1" and REG2 to SICK2? You would need something to JOIN on.
Using the data you have given, I see no way to map those up in any useable manner.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2021 at 8:25 am
Hi Scott,
Man, you are a whiz and when I grow up I wanna be like you 🙂
Just kidding, I'm sure I'm older than you 🙂
This is exactly what I was looking for, thanks a lot and god bless !
DS
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply