sql help, please

• RightOnTarget

  Hall of Fame

  Points: 3041

  More actions

  August 20, 2013 at 11:33 am

  #278918

  Hi all,

  Could someone help me make it work, please?

  I have the following case:

  create table t (col1 varchar(1), col2 varchar(10), col3 int);

  insert into t values ('a','1a',1);

  insert into t values ('a','2a',1);

  select * from t;

  returns:

  a1a1

  a2a1

  I need it to be:

  a1a,2a 1

  Thanks,

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 20, 2013 at 12:03 pm

  #1643191

  Thank you for posting ddl and sample in an easy to use format. You can use STUFF for this.

  select col1,

  STUFF((select ',' + col2

  from t t2

  where t2.col1 = t1.col1 and t2.col3 = t1.col3

  order by t2.col2

  for xml path('')), 1, 1, '') as col2

  , col3

  from t t1

  group by col1, col3

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• RightOnTarget

  Hall of Fame

  Points: 3041

  More actions

  August 20, 2013 at 12:18 pm

  #1643194

  Thank you for providing a solution so quickly.