Post reply

Transpose Rows to Columns according to ID

  • September 1, 2009 at 6:08 am

    #134655

    Hi

    I know there is plenty forums like this, but I can't find one that is exactly what I need...

    Sample Data

    ProjectLinkProjectCodeActiveProject

    2334CFL1001

    2335CFL1010

    2336CFL1021

    2337CFL1031

    2338CFL1040

    2339CFL1050

    2340CFL1061

    2341CFL1070

    2342CFL1081

    ….CFL1090

    10000CFL11000

    I need:

    ActiveProject 1 2 3 .... 10000

    1 CFL100 CFL102 CFL103

    0 CFL101 CFL104 CFL105 CFL1100

    Please help

    Thanks

  • September 1, 2009 at 6:13 am

    #1047443

    I just replied to your previous post regarding the same.

    in this post also, one couldn't get the idea what exactly you want to do.

    It will be helpful if you can provide your sample code if you have tried it anyway OR if you can provide current and required table definitions...

    "Don't limit your challenges, challenge your limits"

  • September 1, 2009 at 7:07 am

    #1047486

    Sorry that came out unclear ...

    This is what I have :

    ProjectLink|ProjectCode| ActiveProject

    ___________________________________________________________________

    2334 | CFL100 | 1

    2335 | CFL101 | 0

    2336 | CFL102 | 1

    2337 | CFL103 | 1

    2338 | CFL104 | 0

    2339 | CFL105 | 0

    2340 | CFL106 | 1

    2341 | CFL107 | 0

    2342 | CFL108 | 1

    …. | CFL109 | 0

    10000 | CFL1100 | 0

    and I need it like this:

    ActiveProject | 2334 | 2335 | 2342 | .... | 10000

    __________________________________________________________________________________________

    1 | CFL100 | NULL | CFL108 | ..... | NULL

    0 | NULL | CFL101 | null | ..... | CFL1100

  • September 1, 2009 at 7:51 am

    #1047530

    Do you know ahead of time how many columns you need for this project, or does it vary depending on how many different are in the table?

    This is what is known in SQL as a Pivot, or Cross Tab query. If the number of columns varies by your data/parameters, then it's a dynamic pivot or cross tab.

    There are a number of articles addressing these, including one in my signature. If you've already seen these, and just need more help writing the code, please see the other article in my signature on 'How to Post sample Data', and repost your data so that someone here can work with it.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

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