Using REVERSE

Question

Post reply

Using REVERSE

April 8, 2021 at 8:45 am

This was removed by the editor as SPAM
April 8, 2021 at 8:47 am

This was removed by the editor as SPAM
April 8, 2021 at 12:48 pm

This was removed by the editor as SPAM

Viewing 8 posts - 16 through 22 (of 22 total)

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

ScottPletcher SSC Guru Points: 101119 More actions · Answer 1

Jeff Moden wrote:

ScottPletcher wrote:

You don't really need a pk or any other column from the main table. Also, I assumed you're rather see blank than NULL for missing levels, but if not, just remove the ISNULL()s and naturally you'll see NULLs instead of blanks.

SELECT ca1.*
FROM #ACME A
CROSS APPLY (
    SELECT
    ISNULL(MAX(CASE WHEN ItemNumber = 1 THEN Item END), '') AS Level1,
    ISNULL(MAX(CASE WHEN ItemNumber = 2 THEN Item END), '') AS Level2,
    ISNULL(MAX(CASE WHEN ItemNumber = 3 THEN Item END), '') AS Level3,
    ISNULL(MAX(CASE WHEN ItemNumber = 4 THEN Item END), '') AS Level4,
    ISNULL(MAX(CASE WHEN ItemNumber = 5 THEN Item END), '') AS Level5,
    ISNULL(MAX(CASE WHEN ItemNumber = 6 THEN Item END), '') AS Level6,
    ISNULL(MAX(CASE WHEN ItemNumber = 7 THEN Item END), '') AS Level7,
    ISNULL(MAX(CASE WHEN ItemNumber = 8 THEN Item END), '') AS Level8,
    ISNULL(MAX(CASE WHEN ItemNumber = 9 THEN Item END), '') AS Level9,
    ISNULL(MAX(CASE WHEN ItemNumber =10 THEN Item END), '') AS Level10
    FROM ( SELECT * FROM dbo.DelimitedSplit8K(A.TreePath, ',') ) AS ds
) AS ca1

Yes, that'll work... until you need to join that back to information in the "real" table.

Huh? You can reference any columns you need from the main table in the CA.

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

ScottPletcher SSC Guru Points: 101119 More actions · Answer 2

For example:

ALTER TABLE #ACME ADD 
    new_column1 uniqueidentifier NOT NULL DEFAULT NEWID(),
    new_column2 int IDENTITY(1, 1) NOT NULL;

SELECT ca1.*
FROM #ACME A
CROSS APPLY (
    SELECT A.new_column1, A.new_column2, ds.*
    FROM (
        SELECT
            ISNULL(MAX(CASE WHEN ItemNumber = 1 THEN Item END), '') AS Level1,
            ISNULL(MAX(CASE WHEN ItemNumber = 2 THEN Item END), '') AS Level2,
            ISNULL(MAX(CASE WHEN ItemNumber = 3 THEN Item END), '') AS Level3,
            ISNULL(MAX(CASE WHEN ItemNumber = 4 THEN Item END), '') AS Level4,
            ISNULL(MAX(CASE WHEN ItemNumber = 5 THEN Item END), '') AS Level5,
            ISNULL(MAX(CASE WHEN ItemNumber = 6 THEN Item END), '') AS Level6,
            ISNULL(MAX(CASE WHEN ItemNumber = 7 THEN Item END), '') AS Level7,
            ISNULL(MAX(CASE WHEN ItemNumber = 8 THEN Item END), '') AS Level8,
            ISNULL(MAX(CASE WHEN ItemNumber = 9 THEN Item END), '') AS Level9,
            ISNULL(MAX(CASE WHEN ItemNumber =10 THEN Item END), '') AS Level10
        FROM ( SELECT * FROM dbo.DelimitedSplit8K(A.TreePath, ',') ) AS ds
    ) AS ds
) AS ca1

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

ScottPletcher wrote:

Jeff Moden wrote:
ScottPletcher wrote:
You don't really need a pk or any other column from the main table. Also, I assumed you're rather see blank than NULL for missing levels, but if not, just remove the ISNULL()s and naturally you'll see NULLs instead of blanks.
SELECT ca1.*
FROM #ACME A
CROSS APPLY (
    SELECT
    ISNULL(MAX(CASE WHEN ItemNumber = 1 THEN Item END), '') AS Level1,
    ISNULL(MAX(CASE WHEN ItemNumber = 2 THEN Item END), '') AS Level2,
    ISNULL(MAX(CASE WHEN ItemNumber = 3 THEN Item END), '') AS Level3,
    ISNULL(MAX(CASE WHEN ItemNumber = 4 THEN Item END), '') AS Level4,
    ISNULL(MAX(CASE WHEN ItemNumber = 5 THEN Item END), '') AS Level5,
    ISNULL(MAX(CASE WHEN ItemNumber = 6 THEN Item END), '') AS Level6,
    ISNULL(MAX(CASE WHEN ItemNumber = 7 THEN Item END), '') AS Level7,
    ISNULL(MAX(CASE WHEN ItemNumber = 8 THEN Item END), '') AS Level8,
    ISNULL(MAX(CASE WHEN ItemNumber = 9 THEN Item END), '') AS Level9,
    ISNULL(MAX(CASE WHEN ItemNumber =10 THEN Item END), '') AS Level10
    FROM ( SELECT * FROM dbo.DelimitedSplit8K(A.TreePath, ',') ) AS ds
) AS ca1
    
Yes, that'll work... until you need to join that back to information in the "real" table.
Huh? You can reference any columns you need from the main table in the CA.

My apologies... a bit of confusion going on here because #ACME, as it is posted in the data by the op, has no columns other than the TreePath. If #ACME were changed to the real table name, then I agree.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldummy79 SSC-Addicted Points: 491 More actions · Answer 4

Thank you for the code Jeffery, that worked perfectly and thank you to everyone else for your input

chris.johnson.0120 SSC-Addicted Points: 491 More actions · Answer 5

Just FYI, the reason you're getting NULLs in the code you have is the PARSENAME function.

This function is designed to return a part of an object name. As objects can only have 4 parts, separated by at most 3 dots, if you try to use it on a string with more than 3 dots it fails.