Pivoting Problem

  • Yes, that would be a valid assumption for the LOB/Attribute relationship.

    These are the only current fields in the data today, but as additional attributes are added it would need to add additional fields. So if I created a new LOB/attribute record called "NewThing" it would need to create a column for 'NewThing' and display the value for the LOBs that have that attribute with its value.

    As for ID/Date availability per record, there is nothing like that in the data.

  • ChrisM, I was looking at your response/solution and it looks close, but it's not handing the duplicate records (where there are two instances of MNS with different XLO values). That's where I have been stumped on how to get around that since there could be duplicate or more for any given LOB value.

  • is this "your" table design.....can you make any changes to it?

    going back to your original post...what results are you expecting from such a scenario as the following?

    RBR Attribute Value

    ------- ---------- ----------

    ONR XLOC ML

    ONR XLOC MS

    ONR XLOC ML

    ONR XLOC MS

    ONR CRE AA

    ONR CRE BB

    ONR CRE CC

    ONR CRE DD

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I can't change the table design as too many production systems would be impacted.

    As for what I would expect for a result...here's what I would hope to see

    LOB XLOC CRE

    ----- ------- ------

    ONR ML AA

    ONR ML BB

    ONR ML CC

    ONR ML DD

    ONR MS AA

    ONR MS BB

    ONR MS CC

    ONR MS DD

  • A little late to the party, but unless I'm missing something (always possible :-))the desired results you gave for J Livingston's sample data seem inconsistent with the desired results from your sample data earlier.

    The desired results from J Livingston's sample data include every combination of the distinct values of each attribute, which was not how the earlier results did it.

    In the earlier posted results for LOB='MNS', for example, there were two distinct values for XLO, and two distinct values for BPL, but instead of having each combination of those two in the desired result (so four rows), there were only two rows.

    If we can get that cleared up, it should be fairly straightforward from there.

    Cheers!

  • Looks like Jacob bet me to it....was going to ask same question.

    I am also wondering what business requirement you are trying to answer.

    with possibly unlimited attributes each having unlimited values.....what will your end report actually demonstrate?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Jacob, due to J Livingston's data set, there would need to be 4 rows for each distinct LOB/XLOC/CRE combination. In the example i provided earlier the MNS had two rows because the all of the data was repeated except the BPL field which had two distinct values for MNS, so since the other data was the same i only resulted in 2 records. In the example J Livingston provided there were two distinct XLOC values and 4 distinct CRE values so I would expect 8 total results.

  • Not all the other values were repeated. There were also two distinct values for XLO for that LOB

    Jacob Wilkins (2/2/2016)


    ...

    In the earlier posted results for LOB='MNS', for example, there were two distinct values for XLO[/i], and two distinct values for BPL, but instead of having each combination of those two in the desired result (so four rows), there were only two rows.

    ...

    Based on your most recent description, though, it sounds like that was just an oversight, and the goal is to get every combination of distinct values. I'll work under that assumption for now and try to get some code together, but let me know if I'm still misunderstanding something.

    Cheers!

  • For a business requirement, I am trying to satisfy a need for each LOB to be listed with distinct rows for each unique attribute and it's unique value with the attribute name as the column and the attribute values listed in relation to the LOB/Attribute combination.

  • I would like to just apologize right now for describing this poorly. I do appreciate whatever assistance you can provide. 🙂

  • silverbullettruck (2/2/2016)


    I would like to just apologize right now for describing this poorly. I do appreciate whatever assistance you can provide. 🙂

    No worries, it's a fairly involved requirement, so such things are bound to happen.

    To start simply, for the sample data you posted, which of the following is the correct result for LOB='MNS' (I've removed the values for columns other than XLO and BPL, since they were all the same, and it's easier to see this way)?

    --Option 1 (the original)

    --Note that there are two different values for both

    --XLO (ML and MS) and BPL (MSEL% and MSUP%)

    INSERT INTO #FinalTest VALUES (LOB,XLO,BPL)

    ( 'MNS', 'ML', 'MSEL%'),

    ( 'MNS', 'MS', 'MSUP%');

    --Option 2

    --This is in line with what you did

    --for J Livingston's sample data

    INSERT INTO #FinalTest VALUES (LOB,XLO,BPL)

    ( 'MNS', 'ML', 'MSEL%'),

    ( 'MNS', 'ML', 'MSUP%'),

    ( 'MNS', 'MS', 'MSEL%'),

    ( 'MNS', 'MS', 'MSUP%');

    Cheers!

  • Option 1 is what I am looking for.

  • just for fun......is the following a correct answer ??

    CREATE TABLE #Test (

    RBR VARCHAR(3) NULL,

    Attribute VARCHAR(10) NULL,

    Value VARCHAR(2)

    )

    INSERT INTO #Test VALUES ('ONR','XLOC','ML')

    INSERT INTO #Test VALUES ('ONR','XLOC','MS')

    INSERT INTO #Test VALUES ('ONR','XLOC','ML')

    INSERT INTO #Test VALUES ('ONR','XLOC','MS')

    INSERT INTO #Test VALUES ('ONR','CRE','AA')

    INSERT INTO #Test VALUES ('ONR','CRE','BB')

    INSERT INTO #Test VALUES ('ONR','CRE','CC')

    INSERT INTO #Test VALUES ('ONR','CRE','DD')

    INSERT INTO #Test VALUES ('ONR','JLS','01')

    INSERT INTO #Test VALUES ('ONR','JLS','02')

    INSERT INTO #Test VALUES ('ONR','JLS','03')

    INSERT INTO #Test VALUES ('ONR','SBB','99')

    INSERT INTO #Test VALUES ('ONR','SBB','98')

    INSERT INTO #Test VALUES ('ONR','TST','HI')

    ;

    WITH

    xloc as (

    SELECT DISTINCT RBR, Value AS XLOC

    FROM #Test

    WHERE (Attribute = 'XLOC'))

    ,

    cre as (

    SELECT DISTINCT RBR, Value AS CRE

    FROM #Test

    WHERE (Attribute = 'CRE'))

    ,

    jls as (

    SELECT DISTINCT RBR, Value AS JLS

    FROM #Test

    WHERE (Attribute = 'JLS'))

    ,

    sbb as (

    SELECT DISTINCT RBR, Value AS SBB

    FROM #Test

    WHERE (Attribute = 'SBB'))

    ,

    tst as (

    SELECT DISTINCT RBR, Value AS TST

    FROM #Test

    WHERE (Attribute = 'TST'))

    SELECT

    xloc.RBR

    , xloc.XLOC

    , cre.CRE

    , jls.JLS

    , sbb.SBB

    , tst.TST

    FROM xloc INNER JOIN

    cre ON xloc.RBR = cre.RBR

    INNER JOIN

    jls ON xloc.RBR = jls.RBR

    INNER JOIN

    sbb ON xloc.RBR = sbb.RBR

    INNER JOIN

    tst ON xloc.RBR = tst.RBR

    ORDER BY xloc.RBR

    , xloc.XLOC

    , cre.CRE

    , jls.JLS

    , sbb.SBB

    , tst.TST

    DROP TABLE #Test

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • In that case I'm still a bit confused, I fear.

    Why do we want all combinations of distinct values with J Livingston's data (4 distinct values for one attribute, 2 for another, so 8 total rows), but not the data in my previous post (2 distinct values for one attribute, 2 for another, but only 2 rows instead of the expected 4)?

    I'm sure there's some simple rule underlying all this, but I haven't figured it out yet 🙂

    Cheers!

  • to pile on with the confusion if I may

    what results required for this set?

    CREATE TABLE #Test (

    RBR VARCHAR(3) NULL,

    Attribute VARCHAR(10) NULL,

    Value VARCHAR(2)

    )

    INSERT INTO #Test VALUES ('ONR','XLOC','ML')

    INSERT INTO #Test VALUES ('ONR','XLOC','MS')

    INSERT INTO #Test VALUES ('ONR','CRE','AA')

    INSERT INTO #Test VALUES ('ONR','CRE','BB')

    INSERT INTO #Test VALUES ('ONR','CRE','CC')

    INSERT INTO #Test VALUES ('ONR','JLS','01')

    INSERT INTO #Test VALUES ('ONR','JLS','02')

    INSERT INTO #Test VALUES ('ONR','JLS','03')

    INSERT INTO #Test VALUES ('ONR','SBB','99')

    INSERT INTO #Test VALUES ('ONR','SBB','98')

    INSERT INTO #Test VALUES ('ONR','TST','HI')

    -- add some new records with a few new attributes

    INSERT INTO #Test VALUES ('ABC','XLOC','1')

    INSERT INTO #Test VALUES ('ABC','CRE','2')

    INSERT INTO #Test VALUES ('ABC','OBJ','3')

    INSERT INTO #Test VALUES ('ABC','DOH','1')

    INSERT INTO #Test VALUES ('ABC','PR','2')

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 15 posts - 16 through 30 (of 51 total)

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