How to group like this

Question

How to group like this

S-322532

SSCommitted

Points: 1613
More actions
January 20, 2009 at 7:33 pm

#97133

Hi,
I'm having the following table
sno Author books
---------------------------------------
1 Emily Wuthering Heights
2 Steve once upon a time
3 Richie Travellers story
4 Emily Oliver Twist
5 Steve Dallas Ranger
6 Richie The Epilogue
I want to extract the information like below
sno Author books
---------------------------------------
1 Emily Wuthering Heights, Oliver Twist
2 Steve once upon a time, Dallas Ranger
3 Richie Travellers story,The Epilogue
Can any one advise me how to achieve this ?
Thanks
Sree

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

hopefully, this is not homework, because if you submit this as your answer, and can't explain what it does, you'll get burned.

FOR XML has a neat way of getting values as a comma delimited list.

SELECT A.sno,A.Author

,STUFF(

(

SELECT ', ' + B.books

FROM @AllBooks B

WHERE A.Author= B.Author

FOR XML PATH(''))

,

1, 2, '')

FROM @AllBooks A

GROUP BY Author

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can also check out the articles in Steve's Tame Those Strings series. Here's a search link for them:

http://www.sqlservercentral.com/search/?cx=000517265726492607871%3Agqdiynsp-y0&cof=FORID%3A9&q=tame+those+strings&sa=Go#1193

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question