query that returns mutliple rows into one row

Question

query that returns mutliple rows into one row

cmckenzie-656927

SSC Veteran

Points: 231
More actions
May 27, 2010 at 1:33 pm

#225082

I have to tables for a report. the first table has customers and the second table has orders for customers. I would like to create a query and/or report in SSRS to show each customer and all orders for that customer in one row rather than displaying the customer info for every order.
eg.
Instead of this
IDNAMEOrders
1HarryOrder1
1HarryOrder2
I want this
IDNAMEOrders
1HarryOrder1, Order2
Any assiatance is appreciated:-)

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

cmckenzie-656927 (5/27/2010)
I have to tables for a report. the first table has customers and the second table has orders for customers. I would like to create a query and/or report in SSRS to show each customer and all orders for that customer in one row rather than displaying the customer info for every order.
eg.
Instead of this
IDNAMEOrders
1HarryOrder1
1HarryOrder2
I want this
IDNAMEOrders
1HarryOrder1, Order2
Any assiatance is appreciated:-)

SELECT ID,

Name,

Order1= max(case WHEN Orders='Order1' then Orders else NULL end),

Order2= max(case WHEN Orders='Order2' then Orders else NULL end)

FROM <YourTable>

GROUP BY ID, Name

If you need more help:

1. see the first link in my signature, then post back here with the information requested there, as well as expected output based upon the sample data provided.

2. See the links in my signature on CROSS TABS/PIVOT.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

If you want to get your results in one column rather than pivoted you could use the FOR XML "trick":

SELECT

ID, NAME,

STUFF((SELECT ', ' + Orders FROM YourTable t2 WHERE t2.ID = t1.ID FOR XML PATH('')),1,2,'') as Orders

FROM

YourTable t1

GROUP BY

ID, NAME

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]

WayneS SSC Guru Points: 95461 More actions · Answer 3

Good going Lutz. I completely missed that he specified one column with CSV output... Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

cmckenzie-656927 SSC Veteran Points: 231 More actions · Answer 4

Thanks lutz. It works but I don't know what you did with the for XML trick so I need to read up on that.

LutzM SSC Guru Points: 107049 More actions · Answer 5

The following two threads might help you to understand the usage of FOR XML as well as STUFF:

http://www.sqlservercentral.com/Forums/FindPost679589.aspx

and

http://www.sqlservercentral.com/Forums/FindPost873866.aspx

The former has some sample code that should help you understand the concept and the latter goes a step further and describe how to deal with "embedded characters such as '&', '<', '>' (and so on)" (to quote a portion Paul's post).

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]