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