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
 



 

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

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

    Regards..
    Cloud Computing Training Chennai

    ReplyDelete

  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.

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

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

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

    ReplyDelete
  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.
    Regards,
    sas training in Chennai|sas course in Chennai|sas institutes in Chennai

    ReplyDelete
  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.
    Regards,

    Informatica training in chennai|Best Informatica Training In Chennai

    ReplyDelete
  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…
    Regards,
    Oracle apps Training in Chennai|Oracle DBA Training in Chennai

    ReplyDelete
  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

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

    ReplyDelete
  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: http://autoneticstraining.com/
    Contact: +91 7721988881 / 7721988882
    0253 6615509

    ReplyDelete
  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 |

    ReplyDelete

  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

    ReplyDelete