SQL Server : Select rows in rows

Question

I have the following table in SQL Server

Names TypeID
-------------
JJ     23
KK     20 
LL     15
JJ     13
KK     18
JJ     10

I want the results of my select query to appear as below

Names TypeID
---------------
JJ    23,13,10
KK    20,18
LL    15

How can i achieve this? Please help, am new to sql

People will generally downvote a question that does not show an attempt to resolve the problem. Thus, I recommend you also post the queries you've tried and the results you got. — Radu Gheorghiu, Sep 29 '14 at 07:01
This question was answered many times before. For example here: http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings A good code snippet is like this: { — WhiteAngel, Sep 29 '14 at 07:03

score 2 · Answer 1 · edited May 23 '17 at 11:45

This question was answered many times before. For example here: Does T-SQL have an aggregate function to concatenate strings?

A good code snippet is like this:

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

This code is taken from the same thread to which I've posted link.

score 1 · Answer 2 · answered Sep 29 '14 at 07:11

drop table #t 
create table #t(names varchar(10),type1 int)
insert into #t values('JJ',23),
('KK',20),
('LL',15),
('JJ',13),
('KK',18),
('JJ',10)

select distinct names,stuff((select ',' +cast(type1 as varchar(10)) from #t t2 where t2.names=t1.names for xml path('') ),1,1,'') as TypeID
from #t t1

FIDDLE DEMO

score 0 · Answer 3 · answered Sep 29 '14 at 07:04

0

Try this:-

SELECT Names, GROUP_CONCAT(TypeID)
FROM YOUR_TABLE
GROUP BY Names;

answered Sep 29 '14 at 07:04

Ankit Bajpai

13,128
4
25
40

Thanks a lot. This was the simplest answer – user3492822 Sep 29 '14 at 07:19
Sorry, this one only works in mysql, not sql server. – user3492822 Sep 29 '14 at 07:32
I had a doubt on it, thats y i asked you to try it. Well all the best in the future. – Ankit Bajpai Sep 29 '14 at 10:55

score 0 · Answer 4 · answered Sep 29 '14 at 07:31

0

try this

SELECT Names, TypeID = 
    STUFF((SELECT ', ' + Convert(nvarchar(2),TypeID)
           FROM your_table b 
           WHERE b.Names = a.Names 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY Names

answered Sep 29 '14 at 07:31

Praloy Das

315
1
6

score -1 · Answer 5 · answered Sep 29 '14 at 07:05

-1

try this code

SELECT name, GROUP_CONCAT(typeid)
FROM test
GROUP BY name

answered Sep 29 '14 at 07:05

Mogli

27
3

SQL Server : Select rows in rows

5 Answers5