transpose rows into columns

  • hello all,

    i have to transpose rows into columns. it goes like this.

    i have a column say for eg: NAME it has three id for COLLEGE.which will

    displayed in rows.i want these id for colleges to transpose into columns.

    "HOW CAN I DO IT USING STORED PROCEDURE" ?

    Can some one provide Sample Code

    Regards,

    hard

    :):)

  • You can use the PIVOT operator for this. It transposes rows into columns. Check out BOL for this. If you any issues then come back here.

    "Keep Trying"

  • sriharivittal1 (5/28/2008)


    Can some one provide Sample Code?

    You want sample code? Then provide some sample data. Please see, read, understand, and follow the article in the link in my signature.

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

  • Take a look at this post: http://www.sqlservercentral.com/Forums/Topic519719-338-1.aspx

  • Here is some sample data:

    ADAMS (See McGee, Angela)

    Adryan (EP)

    12821 N. Stratford #4

    Oklahoma City, OK 73120

    NULL

    ALEXANDER JR. (See Arnold, Alice)

    Alton (RB)

    3818 N. Oak Grove Drive #617

    Midwest City, OK 73110

    NULL

    I have a entire list and i want to move each line into coloumns

    col1 col2 col3

    ADAMS (See McGee, Angela) Adryan (EP) 12821 N. Stratford #4

    etc..

  • It seems like you couldn't find the time to see, read, understand, and follow the article in the link in Jeffs signature...

    [SET SARCASM ON]

    Maybe it's easier if you follow the first link in my signature on how to post sample data.

    [SET SARCASM OFF]

    Just a simple question:

    If you don't take the time to help us help you why should we spend our time working on your issue?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Heh... take a look, Lutz. The person who provided the data isn't even the OP. 😀 I'm out of here.

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

  • Jeff Moden (11/12/2009)


    Heh... take a look, Lutz. The person who provided the data isn't even the OP. 😀 I'm out of here.

    @lcarrethers: I'm sorry!! :blush: Didn't notice that we (still?) have people around doing the job the OP is supposed to do...

    But if you'll ever get in a situation where you feel the need to ask for a SQL solution on this site, the link Jeff and referred to will help to speed up replies...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • would pivot be able to do the following?

    _JobNum_JobName _DivName _PMNames_HrsUsed_DollUsed_JobCost

    12680Mission BeachKing County Johnnie 4605.75211236.41332379.54

    _JobNum12680

    _JobNameMission Beach

    _DivNameKing County

    _PMNamesJohnnie

    _HrsUsed4605.75

    _DollUsed211236.41

    _JobCost332379.54

    Having the column names / field names actually become values columnar..

    Cheers,
    John Esraelo

  • John Esraelo-498130 (11/21/2012)


    would pivot be able to do the following?

    _JobNum_JobName _DivName _PMNames_HrsUsed_DollUsed_JobCost

    12680Mission BeachKing County Johnnie 4605.75211236.41332379.54

    _JobNum12680

    _JobNameMission Beach

    _DivNameKing County

    _PMNamesJohnnie

    _HrsUsed4605.75

    _DollUsed211236.41

    _JobCost332379.54

    Having the column names / field names actually become values columnar..

    This is a three year old thread you just hijacked. Please start your own thread.

    Also you have over 500 points so you should know better than to post like this. You too should see, read, understand, and follow the article in the first link in my signature.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • That's perfectly fine if you do not have an answer.

    Cheers,
    John Esraelo

  • John Esraelo-498130 (11/21/2012)


    That's perfectly fine if you do not have an answer.

    I don't want to get an argument over this but it is not that I don't have an answer. You didn't post a question along the lines of what is considered good etiquette on this forum. You have been around here long enough to know that.

    The direct answer to your question is no. PIVOT will not be able to do what you are asking for by itself. What you want can be done. I would be willing to help if you would post the details to do it. The right thing to do would be to start your own thread for this because it is a different situation than this thread.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • John Esraelo-498130 (11/21/2012)


    would pivot be able to do the following?

    _JobNum_JobName _DivName _PMNames_HrsUsed_DollUsed_JobCost

    12680Mission BeachKing County Johnnie 4605.75211236.41332379.54

    _JobNum12680

    _JobNameMission Beach

    _DivNameKing County

    _PMNamesJohnnie

    _HrsUsed4605.75

    _DollUsed211236.41

    _JobCost332379.54

    Having the column names / field names actually become values columnar..

    No. Unpivot or an "un-crosstab" using CROSS APPLY.

    And Sean is correct... normally better to start a new thread on such things.

    --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 13 posts - 1 through 12 (of 12 total)

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