SQL 2016 - Need help rearranging the output

Question

SQL 2016 - Need help rearranging the output

DiabloSlayer

SSCrazy

Points: 2232
More actions
May 5, 2021 at 4:11 pm

#3879695
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;
```

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

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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".

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

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.

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 3

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