-1

I have results from a table as follows:-

Name           Cat Type
Waugh, John       1
Waugh, John       2
Sampras, Jim      3
Sampras, Jim      4

What I want is this:

Name            Cat Type
Waugh, John       1, 2 
Sampras, Jim      3, 4

How can I accomplish this ? Any ideas and suggestions are greatly appreciated !!

SaiBand
  • 5,025
  • 15
  • 57
  • 76
  • Look for *Group_concat for SQL-Server* – juergen d Mar 31 '14 at 02:51
  • This link should help you: http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ – rcs Mar 31 '14 at 02:52
  • possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Karl Kieninger Mar 31 '14 at 02:52

3 Answers3

0

For t-sql, the best answers are either to use XML PATH or a cte with a numbers table. See, for instance, this question: Concatenate many rows into a single text string?

JoeNahmias
  • 305
  • 1
  • 9
0

Simply do this:

SELECT
      T1.Name, 
      [Cat Type] = STUFF((
          SELECT ',' + convert(varchar(10),T2.[Cat Type])
          FROM TableName T2
          WHERE T1.Name = T2.Name
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM TableName T1
GROUP BY T1.Name
ORDER BY [Cat Type]

Result:

NAME            CAT TYPE
Waugh, John     1,2
Sampras, Jim    3,4

See result in SQL Fiddle.

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
0

If you only have two values, you can do:

select t.name,
       min(t.CatType) + ',' + max(t.CatType) as CatType
from table t
group by t.name;

This assumes that CatType is a string variable. If it is a number, you have to convert it to a string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786