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.

SQL is the language that many different database vendors implement, along with their own custom extensions. Do you know which database PRODUCT (MySQL, SQL Server, Oracle) you are using? — Stuart Ainsworth, Dec 30 '13 at 13:32
Microsoft SQL Server 2005, 2008? oracle 11g? mySQL? vendor and version help. Coalesce doesn't do this. in Oracle it's WM_Concat or ListAgg.) — xQbert, Dec 30 '13 at 13:32
This gives you an example of how to accomplish your desired results. http://stackoverflow.com/questions/12559551/sql-server-equivalent-of-wm-concat-function — xQbert, Dec 30 '13 at 13:34

Mahmoud Gamal · Accepted Answer · 2013-12-30T13:42:37.953

2

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 |

edited Dec 30 '13 at 13:42

answered Dec 30 '13 at 13:35

Mahmoud Gamal

78,257
17
139
164

thanks its working great but its actually cutting some text from text – Vinay Pratap Singh Bhadauria Dec 30 '13 at 13:47
Its cutting the first words of the text – Vinay Pratap Singh Bhadauria Dec 30 '13 at 13:47
@Rex - Can you please update [**this demo**](http://sqlfiddle.com/#!3/6b4b96/13) with the values you are working on to see the issue you have. – Mahmoud Gamal Dec 30 '13 at 13:48
Replaced FOR XML PATH ('')) ,1,2,'') with this FOR XML PATH ('')) ,1,1,'') now working fine – Vinay Pratap Singh Bhadauria Dec 30 '13 at 13:49

Using Coallace in Sql

1 Answers1