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:


Here is the expected output:


Here is the solution: 

--Prepare data
,StudentName VARCHAR(30)
,SubjectName VARCHAR(30)
 INSERT @Student (StudentName, SubjectName) VALUES

-- See input data
SELECT * FROM @Student

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

SELECT StudentName
            (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



  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.

  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


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

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


    Salesforce Administrator Training in Chennai

  5. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Recently I did PHP course at a leading academy. If you are looking for best PHP Training Institute in Chennai visit FITA IT training academy which offer real timePHP Training in Chennai.

  6. Your blog is really awesome and I got some useful information from your blog. This is really useful for me. Thanks for sharing such a informative blog. Keep posting.

    Cloud Computing Training Chennai


  7. Thanks for sharing this information. Java is one of the popular object oriented programming language used for many of the multinational corporation. So learning Java Training in Chennai is really helpful to make a bright future.

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

  9. 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.

  10. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

  11. 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.
    sas training in Chennai|sas course in Chennai|sas institutes in Chennai

  12. 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.

    Informatica training in chennai|Best Informatica Training In Chennai

  13. 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…
    Oracle apps Training in Chennai|Oracle DBA Training in Chennai

  14. 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

  15. 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.

  16. 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:
    Contact: +91 7721988881 / 7721988882
    0253 6615509

  17. 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 |


  18. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Personal Installment Loans
    Title Car loan
    Cash Advance Loan

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

  20. That is very interesting; you are a very skilled blogger. I have shared your website in my social networks! A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article.
    Best AngularJS Training in Chennai
    Hadoop Training in Chennai
    Big Data Training in Chennai

  21. This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation.Your final conclusion was good. We are sowing seeds and need to be patiently wait till it blossoms.
    Fleet Management Software
    Manufacturing ERP
    Logistic ERP
    Logistics Software
    Human resources management software

  22. 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

  23. 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

  24. Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
    Devops Training in pune

    Devops training in tambaram
    Devops training in velachery
    Devops training in annanagar
    DevOps online Training

    Devops Training in Chennai

  25. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.

    rpa training in Chennai | rpa training in pune

    rpa training in tambaram | rpa training in sholinganallur

    rpa training in Chennai | rpa training in velachery

    rpa online training | rpa training in bangalore

  26. I'm here representing the visitors and readers of your own website say many thanks for many remarkable
    python training in Bangalore
    python training in pune
    python online training
    python training in chennai

  27. 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

  28. 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

  29. 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

  30. 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

  31. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi

  32. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    Oracle Training in Chennai | Best Oracle Training Institute in Chennai
    Web Design Training in Chennai
    Web Design Training in Chennai|Best Web Design Training in Chennai
    AngularJS Training in Chennai |Advanced SAS Training in Chennai | Best SAS Training in Chennai

  33. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say
    safety course in chennai

  34. 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

  35. Your blog is interesting for 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

  36. 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

  37. 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

  38. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi | Coimbatore taxi

  39. Interesting information and attractive.This blog is really rocking... Yes, the post is very interesting and I really like it.I never seen articles like this. I meant it's so knowledgeable, informative, and good looking site. I appreciate your hard work. Good job.
    Kindly visit us @
    Sathya Online Shopping
    Online AC Price | Air Conditioner Online | AC Offers Online | AC Online Shopping
    Inverter AC | Best Inverter AC | Inverter Split AC
    Buy Split AC Online | Best Split AC | Split AC Online
    LED TV Sale | Buy LED TV Online | Smart LED TV | LED TV Price
    Laptop Price | Laptops for Sale | Buy Laptop | Buy Laptop Online
    Full HD TV Price | LED HD TV Price
    Buy Ultra HD TV | Buy Ultra HD TV Online
    Buy Mobile Online | Buy Smartphone Online in India

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

  41. The article is very interesting and very understood to be read, may be useful for the people. I wanted to thank you for this great read!! I definitely enjoyed every little bit of it. I have to bookmarked to check out new stuff on your post. Thanks for sharing the information keep updating, looking forward for more posts..
    Kindly visit us @
    Madurai Travels
    Best Travels in Madurai
    Cabs in Madurai
    Tours and Travels in Madurai


  42. Thank you for sharing the article. The data that you provided in the blog is informative and effective. Best Devops Training Institute

  43. 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

  44. 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