February 7, 2011 at 6:10 am
Hi
i want to concatenate rows based on group of values in sqlserver 2000. from sqlserver 2005, we can use it in many ways like XPATH,CTE etc. But how can we do it in 2000.
for example
CREATE TABLE test (id int, name varchar(200))
insert into test (1,'textmsg1')
insert into test (1,'textmsg2')
insert into test (1,'textmsg3')
insert into test (1,'textmsg4')
insert into test (2,'textmsg1')
insert into test (2,'textmsg2')
insert into test (2,'textmsg3')
insert into test (3,'textmsg1')
.
.
.
.
.
.
have 1000's of rows
the Data retrievall must be based on name column i.e. (where name in ('textmsg1','textmsg2','textmsg3')).....
Result should be like
ID Data
1 textmsg1,textmsg2,textmsg3,textmsg4
2 textmsg1,textmsg2,textmsg3
3 textmsg1
Please suggest
Thanks,
Rock
February 7, 2011 at 6:26 am
I stole this code so long ago I'm not sure who I stole it from.
DECLARE @t1 table (ISN int,CatalogName varchar (20) )
DECLARE @t2 TABLE(ISN int,ProdISN int,ProdName varchar(20))
INSERT INTO @t1 VALUES (1,'Ring')
INSERT INTO @t1 VALUES (2,'Necklace')
INSERT INTO @t2 VALUES(1,1,'Wedding')
INSERT INTO @t2 VALUES(1,2,'Engaged')
INSERT INTO @t2 VALUES(1,3,'Lover')
INSERT INTO @t2 VALUES(2,1,'Wedding')
INSERT INTO @t2 VALUES(2,2,'Engaged')
INSERT INTO @t2 VALUES(2,3,'Lover')
DECLARE @Comma varchar(1)
DECLARE @STR varchar (100)
DECLARE @iMax int
DECLARE @oMax int
DECLARE @iCtr int
DECLARE @OCtr int
SET @Comma = ','
SET @Octr = 1
SET @iMax = 3
SET @oMax = 2
WHILE @oCtr <= @oMax
BEGIN
SET @STR = ''
SET @iCtr = 1
WHILE @iCtr <= @iMax
BEGIN
SET @Comma = CASE WHEN @iCtr = 1 THEN '' ELSE ',' END
select b.prodname
from @t1 a
inner join @t2 b
on a.isn = b.isn
where a.ISN = @oCtr
and b.ProdISN = @iCtr
)
SET @iCtr = @iCtr + 1
END
select @STR
SELECT a.CatalogName,@str
FROM @t1 a
INNER JOIN @t2 b
on a.isn = b.isn
WHERE a.ISN = @oCtr
and b.ProdISN = @iCtr -1
SET @oCtr = @oCtr + 1
END
Jim
February 7, 2011 at 6:33 am
rockingadmin (2/7/2011)
Hii want to concatenate rows based on group of values in sqlserver 2000. from sqlserver 2005, we can use it in many ways like XPATH,CTE etc. But how can we do it in 2000.
for example
CREATE TABLE test (id int, name varchar(200))
insert into test (1,'textmsg1')
insert into test (1,'textmsg2')
insert into test (1,'textmsg3')
insert into test (1,'textmsg4')
insert into test (2,'textmsg1')
insert into test (2,'textmsg2')
insert into test (2,'textmsg3')
insert into test (3,'textmsg1')
.
.
.
.
.
.
have 1000's of rows
the Data retrievall must be based on name column i.e. (where name in ('textmsg1','textmsg2','textmsg3')).....
Result should be like
ID Data
1 textmsg1,textmsg2,textmsg3,textmsg4
2 textmsg1,textmsg2,textmsg3
3 textmsg1
Please suggest
Thanks,
Rock
Just download the rows locally on 2k5 and use xpath there.
February 7, 2011 at 4:34 pm
rockingadmin (2/7/2011)
Hii want to concatenate rows based on group of values in sqlserver 2000. from sqlserver 2005, we can use it in many ways like XPATH,CTE etc. But how can we do it in 2000.
for example
CREATE TABLE test (id int, name varchar(200))
insert into test (1,'textmsg1')
insert into test (1,'textmsg2')
insert into test (1,'textmsg3')
insert into test (1,'textmsg4')
insert into test (2,'textmsg1')
insert into test (2,'textmsg2')
insert into test (2,'textmsg3')
insert into test (3,'textmsg1')
.
.
.
.
.
.
have 1000's of rows
the Data retrievall must be based on name column i.e. (where name in ('textmsg1','textmsg2','textmsg3')).....
Result should be like
ID Data
1 textmsg1,textmsg2,textmsg3,textmsg4
2 textmsg1,textmsg2,textmsg3
3 textmsg1
Please suggest
Thanks,
Rock
For SQL Server 2000, please see the following which includes not only the method but some performance problems to avoid.
http://www.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply