How to Pivot values stored as rows not columns

  • Hi all,  I must say at the start that I can do basic SQL so I really don't know where to start on this one so come on this board for some expert help.

    As context, all the Reference data is packed into 3 tables with each row holding only part of the structure.  I have simplified the structure below to show how it all fits together.

    SOURCE_SYSTEM_TABLE_A

    id (PK)

    tableName

    SOURCE_SYSTEM_COLUMN_B

    id(PK)

    SOURCE_SYSTEM_TABLE_A_id

    (FK)columnName

    columnSequenceNumber

    The above tables are filled with all the reference data names and columns e.g.

    SOURCE_SYSTEM_TABLE_A

    id     tableName

    1       refCountry

    2       refContinents

    SOURCE_SYSTEM_COLUMN_B

    id     SOURCE_SYSTEM_TABLE_A_id    columnName     columnSequenceNumber

    100  1                                                                  id                          1

    101   1                                                                  countryName    2

    102   1                                                                 countryCode      3

    103   2                                                                id                           1

    104   2                                                                continentName   1

    Now it gets really confusing, the 3rd table holds the actual values each of the columns:

    COLUMN_VALUE_C

    id(PK)  SOURCE_SYSTEM_TABLE_A_id(fk)   SOURCE_SYSTEM_COLUMN_B_id(fk)   columnValue     rowGroup(guid)

    1000     1                                                                       100                                                                       826                       AAAA

    1001      1                                                                       101                                                                       United Kingdom AAAA

    1002     1                                                                       102                                                                       GBR                       AAAA

    1003     1                                                                       100                                                                       620                        BBBB

    1004     1                                                                       101                                                                       Portugal                BBBB

    1005     1                                                                       102                                                                      PRT                        BBBB

    My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way e.g.

    refCountry

    id              countryName              countryCode

    826          United Kingdom         GBR

    620          Portugal                        PRT

     

    Any pointers would be greatly appreciated.  It's not the sort of table structure I have come across before.

    Many thanks

    Rob

     

  • Not a solution since am at work, but for the future, if you have TSQL Questions, rather than what u did, its much more helpful and easier for you and the forum members if you post something like this. So everyone who wants to take a shot at your question doesnt have to write this by hand to test

    create table #A
    (A_ID int
    ,tablename varchar(40)
    )

    create table #B
    (B_ID int
    ,SOURCE_SYSTEM_TABLE_A_id varchar(40)
    ,columnName varchar(40)
    ,columnSequenceNumber int
    )


    create table #C
    (C_ID int
    ,A_ID int
    ,B_ID int
    ,columnValue varchar(40)
    ,rowGroup varchar(40)
    )

    insert into #A
    values (1,'refCountry'),(2,'refContinents')

    insert into #B
    values
    (100,1,'ID',1)
    ,(101,1,'countryName',1)
    ,(102,1,'countryCode',1)
    ,(103,2,'ID',1)
    ,(104,2,'continentName',1)

    insert into #C
    values
    (1000,1,100,'826','AAAA')
    ,(1001,1,101,'United','AAAA')
    ,(1002,1,102,'GBR','AAAA')
    ,(1003,1,100,'620','AAAA')
    ,(1004,1,101,'Portugal','BBBB')
    ,(1005,1,102,'PRT','BBBB')

    select *
    from #A


    select *
    from #B


    select *
    from #C

    I want to be the very best
    Like no one ever was

  • Ha,  ok I am sorry.  I was trying to explain the best way I could.  Many thanks for the advice,  I will do it that way in future.

    Mike

  • Using the sample data from above (with a couple of correction), you can use a cross tab to achieve what you're after:

    SELECT MAX(CASE B.ColumnName WHEN 'ID' THEN C.ColumnValue END) AS ID,
    MAX(CASE B.ColumnName WHEN 'CountryName' THEN C.ColumnValue END) AS CountryName,
    MAX(CASE B.ColumnName WHEN 'CountryCode' THEN C.ColumnValue END) AS CountryCode
    FROM #C C
    JOIN #B B ON C.B_ID = B.B_ID
    GROUP BY C.rowGroup;

    Note, however, that the real problem here is your design; it's massively denormalised. You really need to fix that. The end result, above, should be how your data is stored, not how you currently have it.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  •  

    The following SQL should get the results as requested in your post.

    SELECT      ID          = MAX( CASE WHEN col.columnName = 'ID'          THEN val.columnValue END )
    , countryName = MAX( CASE WHEN col.columnName = 'countryName' THEN val.columnValue END )
    , countryCode = MAX( CASE WHEN col.columnName = 'countryCode' THEN val.columnValue END )
    FROM SOURCE_SYSTEM_TABLE_A AS tab
    INNER JOIN SOURCE_SYSTEM_COLUMN_B AS col
    ON tab.id = col.SOURCE_SYSTEM_TABLE_A_id
    INNER JOIN COLUMN_VALUE_C AS val
    ON tab.id = val.SOURCE_SYSTEM_TABLE_A_id
    AND col.id = val.SOURCE_SYSTEM_TABLE_B_id
    WHERE tab.tablename = 'refCountry'
    GROUP BY val.rowGroup;

     

    Now, I would suggest that you take the time to familiarise yourself with these 2 articles by Jeff Moden to see how to do it dynamically

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

  • Many thanks for your help both,

    I will have a go in an hour and see whether I can get it working.

    One added bit of complexity (I don't think I explained too well).  TableB holds the structure for each reference data set (the columns for each are different).

    refCounty has:

    id

    CountryName

    CountryCode

    refContinents has:

    id

    ContinentName

    I am also trying to figure out how to inject the structure from TableB somehow into the query so at runtime I only need to provide the tablename, refCountry, refContinents etc.

    Would I need to do this as 2 stages (i.e. retrieve the column structure from TableB) then pass these into the query.

    Sorry if there is a better way to explain this

  • Neither your example data nor requirements mentioned continent.  If you don't come right, then come back with sample data that represents both continent and country, and show us what you have tried.  then we can show you how close you are.

  • Thanks Des,

    If you look at the very first post my example showed both Country and continents as reference data in TableA and TableB (here I was trying to show how the data is actually stored).

    SOURCE_SYSTEM_TABLE_A

    id     tableName

    1       refCountry

    2       refContinents

    SOURCE_SYSTEM_COLUMN_B

    id     SOURCE_SYSTEM_TABLE_A_id    columnName     columnSequenceNumber

    100  1                                                                  id                          1

    101   1                                                                  countryName    2

    102   1                                                                 countryCode      3

    103   2                                                                id                           1

    104   2                                                                continentName   1

    I also tried to explain my requirement:

    "My problem is that I want to do a select across the 3 tables by selecting one of the reference table names in TABLE_A with the results being displayed in the normal way"

     

    I will have a go and let you all know how I get on.

     

     

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)

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