Mutiple row in to single row cell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mutiple row in to single row cell

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-12-17 : 00:58:20

My result data is

ID NOTES
1 A
1 B
1 C
2 D
2 E
2 F

Expexted output is

1 A,B,C
2 D,E,F

Any funstion or ??? need to do in single query ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:01:14

[code]SELECT DISTINCT ID,STUFF((SELECT ','+ NOTES FROM Table WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM Table t[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 01:02:22

SELECT distinct id,STUFF((select ',' + notes from urtable where id = t.id for xml path('')),1,1,' ')from urtable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:04:18

quote:
Originally posted by bklr

SELECT distinct id,STUFF((select ',' + convert(varchar(12) ,notes) from urtable where id = t.id for xml path('')),1,1,' ')from urtable t

by a minute

also you've missed table alias t

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-12-17 : 01:08:53

SELECT DISTINCT custid,STUFF((SELECT ','+ notes FROM q1 WHERE ID=q1.custid FOR XML PATH('')),1,1,'')
FROM q1

--drop table q1
create table q1 (custid int , notes varchar(2) )

insert into q1 values(1,'a')
insert into q1 values(1,'b')
insert into q1 values(1,'c')
insert into q1 values(2,'c')
insert into q1 values(2,'d')
insert into q1 values(2,'a')
=============
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XML'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:11:58

are you using sql 2005?

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-12-17 : 01:15:21

Sorry SQL 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:21:39

quote:
Originally posted by niranjankumark

Sorry SQL 2000

then why you posted in sql 2005 forum?

ok here's the sql 2000 solution

create a function as below


CREATE FUNCTION GetData
(
@ID int
)
RETURNS varchar(8000)
AS
DECLARE @List varchar(8000)
SELECT @List=COALESCE(@List+ ',','') + NOTES 
FROM yourTable
WHERE ID=@ID
RETURN @List
GO

then use it like below

SELECT DISTINCT ID,dbo.GetData(ID)
FROM YourTable

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-12-17 : 01:33:44

Hi thanks .... is there any system defined funtion ?? because dont have rights to create...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:36:50

nope. no system defined function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources