Combine Query Into String

• texpic

  SSCertifiable

  Points: 5887

  More actions

  May 14, 2006 at 9:48 pm

  #91735

  I'm not sure what this is called, so I'm not sure how to search the forum.  I'm sure it has been addressed.  Sorry for asking a question that I'm sure is covered here.

  Lets say I have a table ColorChart that has a field called Colors.  The rows have this data:

  red

  green

  blue

  purple

  green

  How could I get the rows into a single string (example)

  red; green; blue; purple; green

   

  SELECT Colors FROM ColorChart (them make the result of this query a single string)

  Thanks

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 14, 2006 at 10:04 pm

  #637765

  It's under concatenation :

  IF Object_id('ListTableColumns') > 0

  DROP FUNCTION ListTableColumns

  GO

  CREATE FUNCTION dbo.ListTableColumns (@TableID as int)

  RETURNS varchar(8000)

  AS

  BEGIN

  Declare @Items as varchar(8000)

  SET @Items = ''

  SELECT

  @Items = @Items + C.Name + ', '

  FROMdbo.SysColumns C

  WHEREC.id = @TableID

  AND OBJECTPROPERTY(@TableID, 'IsTable') = 1

  ORDER BYC.Name

  SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))

  RETURN @Items

  END

  GO

  Select dbo.ListTableColumns(Object_id('SysObjects'))

  --result :

  --base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype

  DROP FUNCTION ListTableColumns

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  May 15, 2006 at 10:33 am

  #637920

  See:

  http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx (no 5)

  or

  http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

  For your example...

  --data

  declare @ColorChart table (Colors varchar(10))

  insert @ColorChart

            select 'red'

  union all select 'green'

  union all select 'blue'

  union all select 'purple'

  union all select 'green'

  --calculation

  declare @Colors varchar(100)

  SELECT @Colors = isnull(@Colors, '') + Colors + '; ' FROM @ColorChart

  print left(rtrim(@Colors), len(@Colors) - 1)

  --results

  red; green; blue; purple; green

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.