multi rows value in one select statement

Question

multi rows value in one select statement

Minaz Amin

SSChampion

Points: 11052
More actions
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"

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

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]

Minaz Amin SSChampion Points: 11052 More actions · Answer 2

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"

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

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]

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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.

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

Thanks a lot..it worked:)

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