Click here to Skip to main content
14,667,145 members
Rate this:
Please Sign up or sign in to vote.
Hi all,

ID | Value
—- ——–----
1000 1
1000 2
1000 3
1001 4
1001 5
1001 6
1001 7

I need the output

1000 ID: 1,2,3
1001 ID: 4,5,6,7


What will be the sql server query to achieve.
Posted

Rate this:
Please Sign up or sign in to vote.

Solution 1

COALESCE should probably be the answer your question.

Do go to this link to learn how to use COALESCE :
http://www.sqlprof.com/blogs/sqldev/archive/2008/03/31/how-to-list-multiple-rows-of-data-on-same-line.aspx[^]

*Mark as answer if this solves.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

check with this
select * from <tablename> where ID ="1000" 
select * from <tablename> where ID ="1001" 

and reply me what is the status and mark as answer if its correct
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 3

select distinct id,'ID: '+stuff((
select ', '+ cast(value as varchar(100)) from tmp_tnl   where id= a.id FOR XML PATH('')),1,1,'')
from tmp_tnl a
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100