make select statement that show all the column into one row

• Mad-Dog

  SSCertifiable

  Points: 6117

  More actions

  June 4, 2008 at 11:31 am

  #187614

  Hi,

  i need to make a select statement that take a column with lets say 10 rows and insert it into one row separated by (,).

  i know i can do that but I've forget the syntax.

  example:

  if i write this syntax "select name from sys.databases"

  i need the results like

  master,model,tempdb,etc...

  can you help plz?

  THX

• James Raddock

  Mr or Mrs. 500

  Points: 569

  More actions

  June 4, 2008 at 11:36 am

  #824377

  Select the results in a temp table

  Open a forward on read cursor and concatenate into a varchar(max) variable.

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  June 4, 2008 at 11:44 am

  #824383

  James Raddock (6/4/2008)

  ---

  Select the results in a temp table

  Open a forward on read cursor and concatenate into a varchar(max) variable.

  OUCH, stay away from cursors!!!!

  I would suggest ...

  DECLARE @concatRow VARCHAR(MAX)

  -- create a table for testing purposes only

  DECLARE @t TABLE (nameList VARCHAR(MAX))

  -- build the string

  SELECT @concatRow = ISNULL([name] + ',','') + ISNULL(@concatRow,'') FROM sys.databases

  -- insert for demonstration purposes

  INSERT @t

  SELECT LEFT(@concatRow, LEN(@concatRow)-1)

  --show the results

  SELECT * FROM @t

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• Todd Engen

  SSCrazy Eights

  Points: 9869

  More actions

  June 4, 2008 at 11:45 am

  #824385

  Something like this should work too.

  DECLARE @myColumn VARCHAR(MAX)

  SET @myColumn = (SELECT name + ',' FROM sys.databases FOR XML PATH(''))

  PRINT @myColumn

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  June 4, 2008 at 11:48 am

  #824389

  Or - if you need multiple groups - it might look something like this:

  SELECT t1.k1,

  STUFF((SELECT ','+cast(t2.itemnum as varchar(20))

  FROM dbo.items t2

  WHERE t1.K1 = t2.K1 FOR XML PATH('')),1,1,'')

  FROM dbo.items t1

  GROUP BY t1.k1

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Anam Verma

  SSCertifiable

  Points: 5365

  More actions

  June 4, 2008 at 11:44 pm

  #824647

  Todd Engen (6/4/2008)

  ---

  Something like this should work too.

  DECLARE @myColumn VARCHAR(MAX)

  SET @myColumn = (SELECT name + ',' FROM sys.databases FOR XML PATH(''))

  PRINT @myColumn

  Hi todd

  you have to get rid of ',' at the last of the string.

  can use left() for this.

  DECLARE @myColumn VARCHAR(MAX)

  SET @myColumn = (SELECT name + ',' FROM sys.databases FOR XML PATH(''))

  PRINT Left(@myColumn, len(@myColumn)-1)