By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,609 Members | 3,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,609 IT Pros & Developers. It's quick & easy.

sql SELECT to show multiple rows per row as required

P: 1
Is there a way to show multiple rows per row if required?
For example, say the table I currently have show the following data:

Impact to Business, Business Owner Name, Breakdown
1)Application, John Doe, Application
2)Hardware, Joe Smith , Hardware
3)Multiple , John Doe , 1:Hardware, 2: Application

This is how the data is stored in the datatable itself.
Is there a way I can separate the 'Multiple' one to show individual info in different rows? ideally, the table I need is:

Impact to Business , Business Owner Name , Breakdown
1)Application , John Doe , Application
2)Hardware , Joe Smith , Hardware
3)Multiple , John Doe , 1:Hardware, 2: Application
4)Hardware , John Doe , Hardware
5)Application , John Doe , Application

I am using Crystal Reports to fetch data from database, so I cannot create a dummy table that would do the job. Is there a way of doing this using SELECTs?
May 6 '10 #1
Share this Question
Share on Google+
2 Replies


code green
Expert 100+
P: 1,726
I think we will need to see the query.
The 1:Hardware, 2: Application bit doesn't look possible with SELECT
May 6 '10 #2

ck9663
Expert 2.5K+
P: 2,878
Yes, two queries merged by a UNION ALL.

First query should have a WHERE [Impact to Business] <> 'Multiple'. This should return all data that does not need to be parsed.
UNION ALL
Second query is a sub-query that will PIVOT/UNPIVOT the Breakdown column WHERE [Impact to Business] = 'Multiple'

Happy Coding!!!

~~ CK
May 6 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.