|
RE: Appending multiple fields to a single field
Posted: 02 Aug 2007 09:51 AM |
Hi All,
wonder if anyone can help me with this one. I have a dataset that looks like this:
ID Note ID2 Date
01 a note 99 22/01/07
01 another note 92 25/01/07
05 note 32 27/04/07
05 a note 2 32 27/04/07
What I need to do is concatenate all notes together, either in a single field in seperate fields on the same line:
ID Note ID2
01 a note + 25/01/07 + another note 99
05 note + 27/04/07 + a note 2 32
Does anyone know how this can be done, I've tried a few options....
Miles
|
|
|
 |
|
 urid
|
 |
| Joined: 18 Jun 2007 |
| Total Posts: 34 |
| |
|
Re: RE: Appending multiple fields to a single field
Posted: 02 Aug 2007 03:19 PM |
Hi
Since you have not mentioned what is the version you are using I'll provide for both (sql server 2005/2000)
create table t ( id varchar(5) , note varchar(50),
id2 int,dt datetime)
insert into t values ('01','a note',99,'20070122')
insert into t values ('01','another note',92,'20070125')
insert into t values ('05','note',32,'20070427')
insert into t values ('05','a note 2',32,'20070427')
--sql server 2005 only
SELECT m1.id,
( SELECT m2.note + ','+ CAST(id2 AS VARCHAR(20))+','+ CONVERT(VARCHAR(15),dt,112)
FROM t m2
WHERE m2.id = m1.id
ORDER BY note
FOR XML PATH('') ) AS note
FROM t m1
GROUP BY m1.id ;
---sql server 2000
I'd prefer to doing such reports on the client side
create function dbo.fn_my ( @id varchar(5))
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+coalesce(note,'')+','+ coalesce(cast(id2 as varchar(10)),'')+','+
coalesce(convert(varchar(20),dt,112),'')from t where id=@id
return @w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from t
)
as dd
drop function dbo.fn_my
drop table t |
Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
|
|
|
|