Using Coallace in Sql

Question

How can i Convert

Id  Name TypeCode   UseType OptionsText
1    A     AA          1      Test1
2    B     AB          2      Test2
3    B     AB          2      Test3

To this

Id  Name TypeCode   UseType OptionsText
1    A     AA          1      Test1
2    B     AB          2      Test2;;Test3

I am trying to do it with coallace but not able to make it work properly.

Answer 1

You can use the FOR XML to do so:

SELECT 
  t1.Name,
  t1.TypeCode,
  t1.UseType ,
  STUFF((
    SELECT ';; ' + t2.OptionsText
    FROM Tablename t2
    WHERE t2.Name   = t1.Name
      AND t1.TypeCode = t2.TypeCode
    FOR XML PATH (''))
  ,1,2,'') AS OptionsText
FROM Tablename t1
GROUP BY t1.Name,
      t1.TypeCode,
      t1.UseType; 

• SQL Fiddle Demo

This will give you:

| NAME | TYPECODE | USETYPE |    OPTIONSTEXT |
|------|----------|---------|----------------|
|    A |       AA |       1 |          Test1 |
|    B |       AB |       2 |  Test2;; Test3 |