Post reply

T-SQL Self Referencing Join

  • May 1, 2020 at 12:50 am

    Go to Answer

    #3747900

    Hi All,

    I am trying to do a self referencing cross join, i think not sure what else to call it from one table. For each value  get the next attribute/value by policy. I have included screen shots of the current table and the expected output and sample data.

    Hopefully I have provided enough information and sample data.

    Current Table, Pivot_data

    source table

    Required Output

    expected

    Thanks for any assistance.

    • This topic was modified 4 years, 8 months ago by  ringovski. Reason: grammar

    Attachments:
    You must be logged in to view attached files.

  • May 1, 2020 at 1:10 am

    Answer

    #3747904

    Sample data should be provided in a form which can be executed in SSMS, as you should well know.

    DROP TABLE IF EXISTS #Policy;

    CREATE TABLE #Policy
    (
        Policy_Number VARCHAR(20)
       ,Attribute VARCHAR(50)
       ,Value VARCHAR(100)
    );

    INSERT #Policy
    (
        Policy_Number
       ,Attribute
       ,Value
    )
    VALUES
    ('4LZ175386', 'Age_Group', 'Age_Group 60')
    ,('4LZ175386', 'Alarm', 'Alarm N')
    ,('4LZ175386', 'Building_Excess', 'Building_Excess 500')
    ,('4LZ175386', 'Building_Year', 'Building_Year 2010')
    ,('4LZ175386', 'Construction_Group', 'Construction_Group Other Coded')
    ,('4LZ175386', 'Contents_Excess', 'Contents_Excess 250')
    ,('4LZ175386', 'Cover', 'Cover Accidental Dam')
    ,('4LZ175386', 'Cover_Type', 'Cover_Type Combined Cover')
    ,('4LZ175386', 'Dwelling_Type', 'Dwelling_Type HOUSE')
    ,('4LZ175386', 'Loyalty_Group', 'Loyalty_Group 5')
    ,('4LZ175386', 'NCB_Rating', 'NCB_Rating 3')
    ,('4LZ175386', 'Occupancy', 'Occupancy OWNER OCCUPIED')
    ,('4LZ175386', 'Over_50', 'Over_50 Y')
    ,('4LZ175386', 'Pay_Freq_Group', 'Pay_Freq_Group P')
    ,('4LZ175386', 'Region_Area_2011', 'Region_Area_2011 Very Remote Australia')
    ,('4LZ175386', 'Rent_Default', 'Rent_Default N')
    ,('4LZ175386', 'Retiree', 'Retiree N')
    ,('4LZ175386', 'Risk_State', 'Risk_State SA')
    ,('4LZ175386', 'SA4_Name_2011', 'SA4_Name_2011 South Australia - Outback')
    ,('4LZ175386', 'Scheme', 'Scheme INGANZ')
    ,('4LZ175386', 'Seifa_Decile', 'Seifa_Decile 4')
    ,('4LZ175386', 'State_Region_Area', 'State_Region_Area SAC')
    ,('4LZ175386', 'Sum_Insured_Building', 'Sum_Insured_Building 480000')
    ,('4LZ175386', 'Sum_Insured_Contents', 'Sum_Insured_Contents 115000');

    SELECT * FROM #Policy p

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • May 1, 2020 at 2:13 am

    #3747908

    Thanks for the reply, I have resolved the problem with a self referencing LEFT JOIN on the non measure columns.

  • May 9, 2020 at 10:32 pm

    #3750229

    ringovski wrote:

    Thanks for the reply, I have resolved the problem with a self referencing LEFT JOIN on the non measure columns.

    2 way street here... please post your solution!

    --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)

  • May 11, 2020 at 2:07 am

    #3750340

    I am trying to do a self-referencing cross join, i think not sure what else to call it from one table. For each value  get the next attribute/value by policy. I have included screenshots of the current table and the expected output and sample data.  opefully, I have provided enough information and sample data. <<

    There is no concept of "next" in a set-oriented language.

    Your narrative says that you've mixed data and metadata in the same table, thus making this into an EAV nightmare. Have you ever considered using a normalized schema? There cannot be columns called "attribute" or "value" because they're too generic and they are metadata!

    We would prefer that you post DDL that we can actually put into a tool and not make us re-type everything from your screenshots. This is just plain rude.

    A short answer to your question that you don't know quite how to ask is that in the ANSI/ISO Standard SQL and alias is supposed to act as if a completely new table is constructed from the base tables or views given to it. Then the usual rules apply. This means I can write things like

    SELECT ..

    FROM Foobar AS F1

    LEFT OUTER JOIN

    Foobar AS F2

    ON F1.x = F2.x; -- or whatever.

    The idea is that the aft SQL engine will effectively treat the F1 and F2 created working tables as if they are really created tables.

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

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

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