fixed little bit

select
a.idfield ,
a.datafield + '/' + b.datafield
from #t1 a, #t1 b
where a.idfield = b.idfield
and
a.datafield <> b.datafield
and
b.datafield <> a.datafield
Murali Pathivada
"pratclif@.co.alameda.ca.us" wrote:

> Hello All,
> My situation is as follows:
> I have a temp table similar to the one below.
> #tmp1
> idfield datafield
> 001 abcd
> 001 efgh
> 002 aabb
> 003 zzyy
> 003 pppp
> 003 qwrty
> what I want to end up with is:
> #tmp2
> idfield datafield
> 001 abcd/efgh
> 002 aabb
> 003 zzyy/pppp/qwrty
> Any suggestions on how to do this without using a cursor?
> Thanks for all help in advance,
> Cheers,
> P Ratcliff
>Thanks for the quick responses. Unfortunately, I'm still having a
problem. The second suggestion only works if I have a known number of
datafields to insert into the string.
The first suggestion, using the replace, I can't get to work. I am
using SQL 2000 not 2005 and it doesn't appear to recognise the FOR XML.
However, after elminating that, I still get an error on the sub-query
as it returns more than one value.
Any other thoughts? I really (really) don't want to use a cursor here.
Thanks again and cheers,
P Ratcliff|||If you're using SQL Server 2000, then I think
you're out of luck, unless you can do this on the client.
CREATE TABLE mytable(SomePK INT NOT NULL PRIMARY KEY,
idfield CHAR(3),
datafield VARCHAR(5))
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(1,'001','abcd')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(2,'001','efgh')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(3,'002','aabb')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(4,'003','zzyy')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(5,'003','pppp')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(6,'003','qwrty')
GO
CREATE FUNCTION dbo.ConcatDataFields(@.idfield CHAR(3))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.ConcatDataFields VARCHAR(8000)
DECLARE @.DataField VARCHAR(5)
DECLARE myCur CURSOR FOR
SELECT datafield
FROM mytable
WHERE idfield=@.idfield
ORDER BY SomePK
OPEN myCur
FETCH NEXT FROM myCur INTO @.DataField
WHILE @.@.FETCH_STATUS<>-1
BEGIN
SET @.ConcatDataFields = COALESCE(@.ConcatDataFields + '/','') +
@.DataField
FETCH NEXT FROM myCur INTO @.DataField
END
CLOSE myCur
DEALLOCATE myCur
RETURN @.ConcatDataFields
END
GO
SELECT idfield,
dbo.ConcatDataFields(idfield)
FROM mytable
GROUP BY idfield|||Dang, I was afraid of that. I don't have access to the client
application - hence the hoop-jumping. I guess I'll just have to live
with the cursor for the moment.
Thanks again for all your help.
Cheers,
P Ratcliff|||Hi Again,
If you guys are still out there, I came up with a query which will work
-it's REALLY ugly and I was wondering if y'all could help me streamline
it a bit. It does do exactly what I need without using a cursor - but
it's horribly convoluted. In fact, it's so awful, I'm ashamed to post
the code, but here it is:
--sample from original table
create table #realtable(data1 varchar(2), data2 varchar(50))
insert into #realtable(data1, data2) values ('a',NULL)
insert into #realtable(data1, data2) values ('b',NULL)
insert into #realtable(data1, data2) values ('c',NULL)
insert into #realtable(data1, data2) values ('d',NULL)
insert into #realtable(data1, data2) values ('e',NULL)
--data that needs to update original table
create table #tmp(data1 varchar(2), data2 varchar(50))
insert into #tmp(data1, data2) values('a','00001')
insert into #tmp(data1, data2) values('a','00002')
insert into #tmp(data1, data2) values('a','00003')
insert into #tmp(data1, data2) values('b','00001')
insert into #tmp(data1, data2) values('c','00001')
insert into #tmp(data1, data2) values('d','00001')
insert into #tmp(data1, data2) values('d','00002')
insert into #tmp(data1, data2) values('e','00001')
create table #test(data1 varchar(2), data2 varchar(50))
insert into #test (data1)
select distinct data1
from #tmp
declare @.data2string varchar(80),
@.data1string varchar(2),
@.count int
set @.data2string = ''
set @.count = (select distinct count(*) from #test)
while @.count > 0
begin
select @.data1string = tmp.data1
from (select data1 from #tmp) as tmp
select @.data2string = @.data2string + subq.d2 + '/'
from (select t.data1 as d1, t.data2 as d2
from #tmp t
join #test ts
on t.data1 = ts.data1
where t.data1 = @.data1string) as subq
update #test
set data2 = @.data2string
where data1 = @.data1string
set @.data2string = ''
delete from #tmp where #tmp.data1 = @.data1string
set @.count = @.count-1
end
update #realtable
set #realtable.data2 = #test.data2
from #test
where #test.data1 = #realtable.data1
select * from #realtable
drop table #test
drop table #tmp
select * from #realtable
data1 data2
a 00001/00002/00003/
b 00001/
c 00001/
d 00001/00002/
e 00001/
drop table #realtable
I'm wondering at this point if the cursor isn't a better choice. Not to
mention I've recursed myself into a singularity.
Thanks for all your help,
P Ratcliff|||Look mom, without cursors!
CREATE TABLE mytable(SomePK INT NOT NULL PRIMARY KEY,
idfield CHAR(3),
datafield VARCHAR(5))
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(1,'001','abcd')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(2,'001','efgh')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(3,'002','aabb')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(4,'003','zzyy')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(5,'003','pppp')
INSERT INTO mytable(SomePK,idfield,datafield) VALUES(6,'003','qwrty')
go
CREATE FUNCTION dbo.ConcatDataFields
(
@.idfield CHAR(3)
)
RETURNS VARCHAR(8000)
AS
BEGIN
-- something to hold the return value
declare @.ConcatDataFields VARCHAR(8000)
-- do a funky select
select @.ConcatDataFields = isnull((@.ConcatDataFields + '/'),'') +
datafield
FROM Mytable
where Mytable.idfield = @.idfield
-- return the value
return @.ConcatDataFields
END
go
SELECT idfield,
dbo.ConcatDataFields(idfield)
FROM mytable
GROUP BY idfield
hth,
Bart Holthuijsen|||Bart,
You are my hero! Thanks so much for the pointers. I've been stuck with
an application I can't change where the programmer (for reasons best
known to himself) designed the code to expect a string from the query.
Your solution allows me to remove the cursor (oh, so slow) until we can
get the app. changed.
Thanks again!
Cheers,
P Ratcliff