Friday, November 2, 2012

CONCATENATE Column values into Rows


How do you CONCATENATE column values into rows?

Scenario: There is a table Student (ID (identity), StudentName, SubjectName). You need to concatenate all the subject of each student and display along with student name. Below is sample of input data:

ID
StudentName
SubjectName
1
Hari
Science
2
Tony
Biology
3
Hari
Maths
4
Ravi
Physics
5
Ravi
Chemistry
6
Ravi
Botany

 
Here is the expected output:

StudentName
SubjectName
Hari
Science,Maths
Ravi
Physics,Chemistry,Botany
Tony
Biology

 
Here is the solution: 


--Prepare data
 
DECLARE @Student TABLE (
ID TINYINT IDENTITY(1,1)
,StudentName VARCHAR(30)
,SubjectName VARCHAR(30)
)
 INSERT @Student (StudentName, SubjectName) VALUES
 ('Hari','Science')
,('Tony','Biology')
,('Hari','Maths')
,('Ravi','Physics')
,('Ravi','Chemistry')
,('Ravi','Botany')

-- See input data
 
SELECT * FROM @Student



----------------------------------------------------
-- T-SQL for string Concatenation using FOR XML PATH
----------------------------------------------------
DECLARE @Delimit VARCHAR(1) = ','

SELECT StudentName
      ,STUFF(
            (SELECT @Delimit + T1.SubjectName AS [text()]
            FROM @Student T1
            WHERE T1.StudentName = T2.StudentName
            FOR XML PATH(''))
      ,1 , 1, '') AS SubjectName
FROM @Student T2
GROUP BY StudentName
ORDER BY StudentName
 



 

63 comments:

  1. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training Chennai reach FITA Oracle Training Center in Chennai. They give professional and job oriented training for all students.

    ReplyDelete
  2. Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me.. Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai

    ReplyDelete

  3. Hi friends, This is Jamuna from Chennai. Your technical information is really useful for me. Keep update your blog.
    Regards..
    Oracle Training in Chennai

    ReplyDelete
  4. Thanks for sharing your innovative ideas..Its really useful and interesting...

    Regards...

    Salesforce Administrator Training in Chennai

    ReplyDelete
  5. Hi, Your blog is really very informative and useful for me. Thanks for sharing this valuable blog.
    Regards..
    Unix Training

    ReplyDelete
  6. Really awesome blog. Software testing is a method of executing the application or program with the intent of searching the software errors. Testing Training in Chennai offering this course at reasonable cost.

    ReplyDelete
  7. Thanks for sharing this pretty post to our knowledge, SAS is a program that assists to retrieve, managing and uploading the data & simply it’s an integration system of software for performing these actions, thanks for taking your time to discuss about this topic.
    Regards,
    sas training in Chennai|sas course in Chennai|sas institutes in Chennai

    ReplyDelete
  8. Pretty Post! It is really interesting to read from the beginning & I would like to share your blog to my circles for getting awesome knowledge, keep your blog as updated.
    Regards,

    Informatica training in chennai|Best Informatica Training In Chennai

    ReplyDelete
  9. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    Regards,
    Oracle apps Training in Chennai|Oracle DBA Training in Chennai

    ReplyDelete
  10. Thanku for sharing this strategy..
    SAS Institute introduced the SAS Certified Professional Program,training proper understanding of how the SAS software works. Among the five certification programs that SAS Institute has come up with, SAS training can be considered as the entry point into the big data and the data analytics industry.
    SAS online training in hyderabad

    ReplyDelete
  11. good sample of sql server thanks for sharing this and i refer for best SAP HANA Online Training SAP HANA Online Training
    it's a good platform than any other database engines.get certified and full knowledge on the course.

    ReplyDelete
  12. Automation engineering is all about selecting, integrating, configuring and troubleshooting of various readymade products in different engineering branches which makes the machine run automatically. Autonetics helps to reduce gap between industry and yourself, helps you according to current market trend and industry need. Autonetics provide you portal to meet your professional characteristic and make you industry ready professional. Autonetics offers certification course in PLC Training programs for B.E. and Diploma graduating under and working profession. For a better career and higher post opportunities join Autonetics Training Center.
    To know more visit: http://autoneticstraining.com/
    Contact: +91 7721988881 / 7721988882
    0253 6615509

    ReplyDelete
  13. Besant Technologies has done creative work in Training through its Software Testing Course. We offer short-term modular and comprehensive programs to Software Testing career aspirants and working professionals. Candidates can choose between full-time and part-time Software Testing Course options based on the individual’s time constraints. So join us besant technologies. Software Testing Training Institute in Chennai |
    Selenium Training Institute in Chennai |

    ReplyDelete
  14. Great effort. Thanks to shared this informative details with us. Keep updating.
    DBA course syllabus | DBA training courses

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.


    Best Java Training Institute Chennai

    ReplyDelete
  17. Outstanding blog post, I have marked your site so ideally I’ll see much more on this subject in the foreseeable future.
    MEAN stack training in Chennai
    MEAN stack training in bangalore
    MEAN stack training in tambaram
    MEAN stack training in annanagar

    ReplyDelete
  18. Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me.

    java training in marathahalli | java training in btm layout

    java training in jayanagar | java training in electronic city

    ReplyDelete
  19. Thanks for sharing such a great information of concatenate column values into rows.Its really nice and informative great content of different kinds of the valuable information's.

    Android Training
    Android Training in Chennai

    ReplyDelete
  20. This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
    Data science training in tambaram | Data Science training in anna nagar
    Data Science training in chennai | Data science training in Bangalore
    Data Science training in marathahalli | Data Science training in btm

    ReplyDelete
  21. Do you have a spam issue on this website; I also am a blogger, and I wanted to know your situation; many of us have developed some nice methods
    safety courses in chennai

    ReplyDelete
  22. I have never read more interesting articles than yours before. You make me so easy to understand and I will continue to share this site. Thank you very much and more power!

    Selenium Training in Chennai
    Selenium Training
    iOS Training in Chennai
    French Classes in Chennai
    Big Data Training in Chennai
    Android Training
    Android Training in Chennai

    ReplyDelete
  23. Your blog is interesting for readers.you have developed your blog informations with such a wonderful ideas and which is very much
    useful for the readers.i enjoyed your post and i need some more articles also please update soon.
    German Courses in T nagar
    German Course in Anna Nagar
    german language classes in bangalore
    learn german in bangalore

    ReplyDelete
  24. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way. T hank you so much.
    Selenium testing training in chennai
    Selenium course
    Software testing selenium training
    Software Testing Training Institutes in Chennai
    software testing course
    testing courses in chennai

    ReplyDelete
  25. Informative post,It is useful for me to clear my doubts.I hope others also like the information you gave in your blog.
    best vmware training in bangalore
    vmware certification bangalore
    vmware Course in Anna Nagar
    vmware Courses in Tnagar

    ReplyDelete
  26. It is an amazing post.I have found really uOoty Taxi
    Ooty Taxi serviceseful.Keep up the good work.

    ReplyDelete
  27. Excellent Blog. I really want to admire the quality of this post. I like the way of your presentation of ideas, views and valuable content. No doubt you are doing great work. I’ll be waiting for your next post. Thanks .Keep it up! Are you interested to buy luxury boxes... then click here Wallet Box | Perfume Box Manufacturer
    Candle Packaging Boxes
    Luxury Leather Box | Luxury Clothes Box
    Luxury Cosmetics Box | Shoe Box Manufacturer | Luxury Watch Box

    ReplyDelete
  28. Wow, what an awesome spot to spend hours and hours! It's beautiful and I'm also surprised that you had it all to yourselves!
    Kindly visit us @ Best HIV Treatment in India | Top HIV Hospital in India
    HIV AIDS Treatment in Mumbai | HIV Specialist in Bangalore
    HIV Positive Treatment in India | Medicine for AIDS in India

    ReplyDelete
  29. For Hadoop Training in Bangalore visit: Hadoop Training in Bangalore

    ReplyDelete
  30. This comment has been removed by the author.

    ReplyDelete
  31. I really like looking through an blog article that can make people think. Also, many thanks for allowing for me to comment!

    ReplyDelete
  32. Unique Information Articles, Giving informative blogs about Technical study is very useful for feature. Appreciating all for this good work, looking towards more

    python training in chennai | python training in annanagar | python training in omr | python training in porur | python training in tambaram | python training in velachery

    ReplyDelete
  33. It's impressive to visit your site...Enjoying while Reading the Articles...This part of Information's are Helped a lot to me...Keep Going
    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  34. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    data science training in vijayawada

    ReplyDelete
  35. It is really an awesome blog. Thank you very much for sharing this.
    Website Designing company in Delhi

    ReplyDelete
  36. Nice article it is very useful thank you for sharing. oracle training in chennai

    ReplyDelete
  37. Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post. It is really very helpful for us and I have gathered some important information from this blog.
    Java Training in Chennai

    Java Training in Velachery

    Java Training inTambaram

    Java Training in Porur

    Java Training in Omr

    Java Training in Annanagar

    ReplyDelete
  38. Wonderful article, very useful and well explanation. Your post is extremely incredible. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details
    Software Testing Training in Chennai

    Software Testing Training in Velachery

    Software Testing Training in Tambaram

    Software Testing Training in Porur

    Software Testing Training in Omr

    Software Testing Training in Annanagar

    ReplyDelete
  39. To be honest your article is informative and very helpful. Hp Laptop | Hp laptop online price

    ReplyDelete
  40. Awesome informative blog ,Very good information thanks for sharing such wonderful blog with us ,after long time came across such knowledgeble blog. keep sharing such informative blog with us.
    SQL Server DBA Training in Bangalore

    ReplyDelete
  41. Hi, Thank you for this informative blog, I have just started to learn sql online and this blog is really informative for me. Thank you for this blog!

    ReplyDelete
  42. I'm totally playing your web website. You completely have a couple of willing intensity and charming stories. lovely and entirely lighthearted site on the web. Love to watch. keep up with Rocking. Spyhunter Registration Key

    ReplyDelete
  43. IDM could be a program that grants you to move accounts of people holding nothing back on the web. Internet Download Manager 64 Bit

    ReplyDelete