Post reply

multi rows value in one select statement

  • December 13, 2007 at 6:15 am

    #75823

    Hi,

    I have a table named app_degree_info

    which have app_id, idx,app_degree_cd as columns

    idx is the value which gives no of degree applicants is holding startting from zero value.

    I need to design a report with app_is and all the degree_cd . I am not able to design the query as Iam very new to TSQ programming. Need your help.

    ID IDX APP_DEGREE_ID

    07122430006 0 300001

    07122430006 1 300006

    07122450004 0 400001

    071233850001 0 2900006

    071233850001 1 2900007

    Thanks !!

    "More Green More Oxygen !! Plant a tree today"

  • December 13, 2007 at 6:23 am

    #759793

    Is what you provided above the example of your data or an example of what you are looking for? Knowing both of those pieces of information will help us to help you better.

    Thanks.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • December 13, 2007 at 6:30 am

    #759794

    Thanks for the prompt reply.

    Thats the data in the table. My requirement is I should be able to display

    app_id, degree_cd ( list of degrees)

    "More Green More Oxygen !! Plant a tree today"

  • December 13, 2007 at 6:40 am

    #759797

    Are you looking for...

    07122430006 300001

    07122430006 300006

    Or

    07122430006 300001,300006

    Or something else entirely?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • December 13, 2007 at 6:44 am

    #759800

    I belive you are looking for a PIVOT like this

    SELECT [ID],

    MAX(CASE WHEN IDX = 0 THEN APP_DEGREE_ID ELSE null END) AS [Degree0],

    MAX(CASE WHEN IDX = 1 THEN APP_DEGREE_ID ELSE null END) AS [Degree1],

    MAX(CASE WHEN IDX = 2 THEN APP_DEGREE_ID ELSE null END) AS [Degree2]

    FROM [app_degree_info]

    GROUP BY [ID]

    this is fixed to 3 degree columns, if the number is variable then you will have to set a fixed maximum or use dynamic sql

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 13, 2007 at 7:10 am

    #759812

    Thanks a lot..it worked:)

    "More Green More Oxygen !! Plant a tree today"

Viewing 6 posts - 1 through 5 (of 5 total)

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