concatenate Rows using group by

• RockingAdmin

  SSCrazy

  Points: 2828

  More actions

  February 7, 2011 at 6:10 am

  #244895

  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

• skt5000

  SSC-Addicted

  Points: 479

  More actions

  February 7, 2011 at 6:26 am

  #1283057

  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

  SET @STR = @STR + @Comma+(

  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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 7, 2011 at 6:33 am

  #1283068

  rockingadmin (2/7/2011)

  ---

  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

  Just download the rows locally on 2k5 and use xpath there.

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  February 7, 2011 at 4:34 pm

  #1283489

  rockingadmin (2/7/2011)

  ---

  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

  For SQL Server 2000, please see the following which includes not only the method but some performance problems to avoid.

  http://qa.sqlservercentral.com/articles/Test+Data/61572/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions