Rows to columns with a twist

  • Hi all,

    This is sort of a convert rows to columns problem, but it's a little more difficult and causing me major hassles.

    I have a table with 2 columns and ANY number of rows (anywhere from 0 to 100).

    Now, I want to get a table with a single row containing values of the second column of the original table, but I also want the values from the first column to now be the table column names.

    So for example, if we start with the following table:

    TABLE1 (Col1, Col2)

    VALUES ('cat', 4)

    VALUES ('dog', 2)

    VALUES ('rat', 4)

    VALUES ('hat', 1)

    VALUES ('pet', 2)

    VALUES ('cow', 5)

    The result would be the following new table:

    TABLE1 (cat, dog, rat, hat, pet, cow)

    VALUES (4, 2, 4, 1, 2, 5)

    Is this possible?  Any help would be appreciated.

    Cheers,

    Paul

  • I strongly suspect that your example data is missing something, but the following will do for what you have... lemme guess... spreadsheet output?

    --=======================================================

    --      This is just setting up for the test and is

    --      NOT part of the solution.

    --=======================================================

    --===== Create a test table from the data given

     CREATE TABLE Table1 (Col1 VARCHAR(10), Col2 INT)

     INSERT INTO Table1(Col1,Col2)

     SELECT 'cat', 4 UNION ALL

     SELECT 'dog', 2 UNION ALL

     SELECT 'rat', 4 UNION ALL

     SELECT 'hat', 1 UNION ALL

     SELECT 'pet', 2 UNION ALL

     SELECT 'cow', 5

    --=======================================================

    --      Demo the solution

    --=======================================================

    --===== Declare some variables for dynamic SQL

    DECLARE @SQLHead VARCHAR(8000)

    DECLARE @SQLBody VARCHAR(8000)

    DECLARE @SQLFoot VARCHAR(8000)

    --===== Populate the variables using info from the table

        SET @SQLHead = 'SELECT'

     SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)

                     + 'SUM(CASE WHEN Col1 = '''

                     + Col1 + ''' THEN Col2 ELSE 0 END) AS '

                     + Col1

       FROM Table1

     SELECT @SQLFoot = CHAR(13) + 'FROM Table1'

    --===== Print the command we formed and execute it

      PRINT @SQLHead+@SQLBody+@SQLFoot

      EXEC (@SQLHead+@SQLBody+@SQLFoot)

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

  • Thanks!

  • Jeff,

    Even though this kind of dynamic sql leaves a lot of room for generating incomprehensible code, I'm interested in getting to know it better. Any place you recommend with loads of good examples?

    Thanks.

  • Don't know of any coherent place dedicated to just doing crosstabs or just to dynamic SQL... there's a lot of really bad examples that use cursors, as well.  But, if you search this forum for Crosstab, Dynamic, @SQL, and @MySQL, you'll find loads.  Like I said, be careful... and consider carefully if you actually need to do something like this... some folks tend to overuse/abuse the technique.  On the flip side, if you do need it, there's no substitute.

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

  • I was trying to understand what the code Jeff presented was doing and ran the following queries:

    select null + ',' as addedtonull

    select isnull(null,'yep') as justnull

    select isnull(null + ',','yep') as addtonull

    Why does addtonull come up as just "y" instead of "yep"?


    Steve Eckhart

  • Go touch a null and you become a null. That's the rule of nulls.

    Thus null +',' is equal to a null. That's why isnull(null + ',','yep') evaluates to 'yep'.

    No matter where it hides it will show its presence in the form of a null.

    Thus 1+(2+(3+(4+null))) equals null.

    Of course, there are cases when nulls simply get ignored.

    But sometimes nulls wreak havoc.

    Sometimes nulls are a blessing.

    Most of the time they are a pain in the buttock.

    PS

    Now that I've tried it, I'm stumped. It evaluates to 'y'! I can't believe it! So my part about nulls wreaking havoc is true!

    But isnull(null + 'ab','yep') evaluates to 'ye'

    and isnull(null + 'abc','yep') evalutates to 'yep'

    Yep, there is problem.

  • THAT, Ladies and Gentlemen, is why doing it this way is so important...

    DECLARE @ANull VARCHAR(8000)

        SET @ANull = NULL --was already null, just doing it here for effect

    select @ANull + ',' as addedtonull

    select isnull(@ANull,'yep') as justnull

    select isnull(@ANull + ',','yep') as addtonull

     

    --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 9 posts - 1 through 8 (of 8 total)

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