Post reply

Pivot Row to Columns

  • January 10, 2018 at 5:48 am

    #402823

    I have a table "tblPerson" that only has three columns hder, hderOrder  and key. The hderOrder is the order that the hder,  This table is column header information only and the order of the columns.  It has no source data.
    tblPerson:

    hder  hderOrderKey
    Peter    11
    Ray    21
    Gill      31
    Jeff      41
    Mary12
    Sue22
    Jessica32
    Grace42

    I want to create a dynamic query that only has column names where key=1 that looks like this:
    Peter    Ray      Gill       Jeff

    However the source data for the "tblPerson" is in another table name "tblPersonData" that matches up with the column names.

    I want to make this dynamic because I don't know how many columns may be in the tblPerson table for a given key.  So, I need a way to list all the columns headers (hder) and then dynamically add the source data that matches the hder.

    I was trying a PIVOT  but not sure how to structure it.  Maybe a temp table as the output but because it is dynamic, not sure if temp table can be dynamic.

    Please help to send me in the right direction.

    Charles P.

  • January 10, 2018 at 6:08 am

    #1975256

    I'd recommend that you read the following articles:
    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 10, 2018 at 7:02 pm

    #1975431

    Charles_P - Wednesday, January 10, 2018 5:48 AM

    I have a table "tblPerson" that only has three columns hder, hderOrder  and key. The hderOrder is the order that the hder,  This table is column header information only and the order of the columns.  It has no source data.
    tblPerson:

    hder  hderOrderKey
    Peter    11
    Ray    21
    Gill      31
    Jeff      41
    Mary12
    Sue22
    Jessica32
    Grace42

    I want to create a dynamic query that only has column names where key=1 that looks like this:
    Peter    Ray      Gill       Jeff

    However the source data for the "tblPerson" is in another table name "tblPersonData" that matches up with the column names.

    I want to make this dynamic because I don't know how many columns may be in the tblPerson table for a given key.  So, I need a way to list all the columns headers (hder) and then dynamically add the source data that matches the hder.

    I was trying a PIVOT  but not sure how to structure it.  Maybe a temp table as the output but because it is dynamic, not sure if temp table can be dynamic.

    Please help to send me in the right direction.

    Charles P.

    Charles... you're a bit new to these forums so, first of all, WELCOME!  Second of all, great description but if you'd like folks to spill some working code for you, take a look at the article at the first link in my signature line below under "Helpful Links" about how to create "readily consumable data" and there's a pretty good chance that people will respond much more quickly to your post and they'll frequently respond with tested code.

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

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

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