4

Suppose I have a row of data, store such as the following:

    ------------------------
   | Col 1 | Col 2 | Col 3  |
   |------------------------|
   |  Foo  |  Bar  | Foobar |

How might I concatinate this into a single string, such as the below?

Foo-Bar-Foobar

The column headings (and number of column headings) in this table will not be known, so selecting by column name is not an option(?).

Please note that I am not trying to concatinate a list of values in a column, I am trying to concatinate the values stores in one single row. I would also prefer to avoid using pivots, as I will be working with large sets of data and do not want to take the hit to performance.

J. Chapman
  • 305
  • 1
  • 3
  • 9
  • Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Hintham Oct 16 '18 at 08:14
  • 1
    @Chapman Will you need ALL of the table's columns? If not, which columns will you keep? How will the order of the string be determined(eg how do you know to make Foo-Bar-Foobar and not Foobar_foo_bar)? – George Menoutis Oct 16 '18 at 08:16
  • What version of SQL Server are you using? – Eric Brandt Oct 16 '18 at 08:18
  • The order will not matter and all columns should be included within the string. The SQL server version that we are running is 11.0.6260. – J. Chapman Oct 16 '18 at 08:33

4 Answers4

2

In such cases I really adore the mighty abilities of XML in dealing with generic sets:

SELECT STUFF(b.query('
                        for $element in ./*
                        return
                        <x>;{$element/text()}</x>
                       ').value('.','nvarchar(max)'),1,1,'')
FROM 
(   
    SELECT TOP 3 * FROM sys.objects o FOR XML PATH('row'),ELEMENTS XSINIL,TYPE
) A(a)
CROSS APPLY a.nodes('/row') B(b);

The result

sysrscols;3;4;0;S ;SYSTEM_TABLE;2017-08-22T19:38:02.860;2017-08-22T19:38:02.867;1;0;0
sysrowsets;5;4;0;S ;SYSTEM_TABLE;2009-04-13T12:59:05.513;2017-08-22T19:38:03.197;1;0;0
sysclones;6;4;0;S ;SYSTEM_TABLE;2017-08-22T19:38:03.113;2017-08-22T19:38:03.120;1;0;0

Remarks

Some things to mention

  • I use the ; as delimiter, as the - might break with values containing hyphens (e.g. DATE)
  • I use TOP 3 from sys.objects to create an easy-cheesy-stand-alone sample
  • Thx to Zohard Peled I added ELEMENTS XSINIL to force the engine not to omit NULL values.

UPDATE Create JSON in pre-2016 versions

You can try this to create a JSON-string in versions before 2016

SELECT '{' 
      + STUFF(b.query('
                        for $element in ./*
                        return
                        <x>,"{local-name($element)}":"{$element/text()}"</x>
                       ').value('.','nvarchar(max)'),1,1,'')
      + '}'
FROM 
(   
    SELECT TOP 3 * FROM sys.objects o FOR XML PATH('row'),TYPE
) A(a)
CROSS APPLY a.nodes('/row') B(b);

The result

{"name":"sysrscols","object_id":"3","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2017-08-22T19:38:02.860","modify_date":"2017-08-22T19:38:02.867","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}
{"name":"sysrowsets","object_id":"5","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2009-04-13T12:59:05.513","modify_date":"2017-08-22T19:38:03.197","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}
{"name":"sysclones","object_id":"6","schema_id":"4","parent_object_id":"0","type":"S ","type_desc":"SYSTEM_TABLE","create_date":"2017-08-22T19:38:03.113","modify_date":"2017-08-22T19:38:03.120","is_ms_shipped":"1","is_published":"0","is_schema_published":"0"}

Hint

You might add ELEMENTS XSINIL to this query as well. This depends, if you'd like NULLs to simply miss, or if you want to include them as "SomeColumn":""

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • How will this handle nulls? – Zohar Peled Oct 16 '18 at 08:55
  • @ZoharPeled, that was easy, just added `ELEMENTS XSINIL`. This will force the engine to generate an empty element, which will lead to an empty value in the CSV (repeating semicolons). – Shnugo Oct 16 '18 at 09:02
  • Well, I've already upvoted your answer. I was going with a cumbersome solution creating sql based on information_schema.columns, but this is much more elegant. – Zohar Peled Oct 16 '18 at 09:04
  • Thanks for your answer, this works well for my purposes, which one exception: formatting. Some columns in my data contain the decimal 0. When this value is extracted, it is expressed as '.000000000000000e+000'. Any idea how I might prevent it from being formatted in this way? – J. Chapman Oct 16 '18 at 09:48
  • @J.Chapman The XML is not aware of the type, it will return the content *as is*. Of course it was possible to add a lot of *high-sophisticated* business-logic, but this will create some rather big effort. You can include the *schema* into the XML and use internal references in the `XQuery` or you create some statement from `INFORMATION_SCHEMA.COLUMNS`. Or you might just add a *string-based* text-replace for `'.000000000000000e+000'` into `'0'` – Shnugo Oct 16 '18 at 10:05
1

Simply do a SELECT CONCAT(col1,col2,col3) FROM table

However if you wish to make it neat

Use: SELECT CONCAT(col1,'-',col2,'-',col3) FROM table.

Find more help here.

Rigel1121
  • 2,022
  • 1
  • 17
  • 24
Shalvin
  • 188
  • 1
  • 1
  • 12
1

I use UnitE and this is what I would use to select the columns dynamically from the person table.

INFORMATION_SCHEMA.COLUMNS stores the column list for the table and the SELECT statement is built around that.

Declare @Columns NVARCHAR(MAX)
Declare @Table varchar(15) = 'capd_person'

SELECT @Columns=COALESCE(@Columns + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME=@Table )  

EXEC('SELECT DISTINCT ' + @Columns + ' FROM ' + @Table)

You would need to change the EXEC command to suit your needs, using CONCAT as described before.

JonTout
  • 618
  • 6
  • 14
  • The *quirky update* with `SELECT @SomeVariable = @SomeVariable + ...` can have unexpected side effects... There are better approaches to concatenate a list of values. Typically people use `FOR XML` and with versions from 2016+ there is `STRING_AGG()`... – Shnugo Oct 16 '18 at 09:07
  • @Shrugo, I work in the Education Public Sector, we're still on 2008 R2 ;-) – JonTout Oct 16 '18 at 09:10
  • 1
    FOR XML with native XML-support has been introduced in v2005 ;-) If you want, you might try the query in my answer. This should work in your system without any troubles... – Shnugo Oct 16 '18 at 09:12
0

An improved version of JonTout's answer:

    Declare @Columns NVARCHAR(MAX)
    Declare @Table varchar(15) = 'TableName'

    SELECT @Columns=COALESCE(@Columns + '+', '') +'CONVERT(varchar(max),ISNULL('+ COLUMN_NAME+',''''))+''-'''
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME=@Table ) 


    EXEC('SELECT ' + @Columns + ' FROM ' + @Table)
Sahi
  • 1,454
  • 1
  • 13
  • 32