Post reply

taking rows from a table, and making them columns (join construct)

  • July 6, 2009 at 4:31 am

    #211878

    i'm wondering if what i'm trying to do is possible.

    i've got an SP that brings back one row for each customer

    ID,Name,JoinDate,type

    i now want the SP to bring back extra columns depending on what type of customer they are.

    to do this i created another table which, currently, i have it detailing each column as a new row.

    select

    1 as id, 101 as newcol,3 as type

    union

    select

    2,203,3

    union

    select

    3,204,3

    i would now like to add these rows as columns within my SP.

    ID,Name,JoinDate,type,101,203,204

    i've set up the new table in this manner as extra columns (rows) may need to be added to it at a future date.

    is this possible?

    is there another way to do this?

    thanks

  • July 6, 2009 at 4:44 am

    #1020275

    Hi ,

    This should answer your question

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/


    Clear Sky SQL
    My Blog[/url]

  • July 6, 2009 at 4:50 am

    #1020278

    Look up PIVOT in Books Online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 6, 2009 at 5:20 am

    #1020290

    thanks for the pointers.

    i've tried the PIVOT but i'm not sure its quite what i'm looking for.

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/24ba54fc-98f7-4d35-8881-b5158aac1d66.htm

    this example is taking the 'average cost' from each row of the table and putting them all into the same row.

    they're also hardcoding the columns as [0],[1] etc so as to match the rows.

    i dont want to hard code the columns, instead i want to take the value of 'newcol' and use that has the column name.

    this is because customer type '1' may need columns 301 and 302. but customer type '2' may need columns 303 and 304

  • July 6, 2009 at 7:30 am

    #1020358

    Hi you can use "dynamic pivot" tables.

    Here is a sample at T-SQL Pivot Tables in SQL Server - Pivot Table Tutorial with Examples

    In pivot tables are actually you build the query as a string then execute it.

    I hope that helps,

    Eralper

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 6, 2009 at 10:04 am

    #1020487

    thanks for the replys.

    i finally got GilaMonster PIVOT suggestion to work. i hadn't fully understood the example in BOL.

    all works great now. thanks!

  • July 7, 2009 at 4:34 am

    #1020950

    hi all,

    i'm back 😀

    my initial sql query works fine now.

    i've used the pivot function and its great. but now i need to add an extra bit which leads me to believe i need to PIVOT again. is this possible?

    the basic gist of my code is

    select * from

    (select

    ID,

    Type,

    ProfiledDate,

    Unit

    from table1

    left join table2 on table1.ID = table2.table1ID)x

    pivot

    (

    max(ProfiledDate)

    for Unit in ([101],[102])

    )

    but now i want to PIVOT another column (actual date) in the same way so that it brings back the profileddate and actualdate for each unit, within the same table that is brought back.

    i've looked searched through what PIVOT brought back in BOL but can't find anything. which leads me to believe its not possible to do this way. but is there another way to achieve the same result?

  • July 7, 2009 at 9:27 am

    #1021157

    not to worry. i find out a work around.

    for those of you who are interested its from this link

    http://www.codeproject.com/KB/database/Pivot2Columns.aspx

Viewing 8 posts - 1 through 7 (of 7 total)

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