Flatten SQL Server Table to a string

Question

I have a table like:

ID  | Value
----------------
 1  | One
 2  | Two
 3  | Three

What I need to do is create a single string from these values, in the format:

'1: One, 2: Two, 3: Three'

I know how to do this using cursors, but it will be used as a column in a view, so it's not really a performant option. Any pointers?

score 5 · Accepted Answer · answered Feb 09 '11 at 12:18

5

WITH T(ID,Value) AS
(
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two' UNION ALL
SELECT 3, 'Three' 
)

SELECT  STUFF(
        (SELECT ', ' + CAST(ID as varchar(11)) + ': ' + Value
         FROM T
         FOR XML PATH (''))
     , 1, 2, '')

answered Feb 09 '11 at 12:18

Martin Smith

438,706
87
741
845

Thx, XML PATH did the trick. I knew I was missing something, just could not put my finger on it. – SWeko Feb 09 '11 at 12:27
@SWeko - Astander's answer is safer if your data can contain any of `<`, `>`, `&` (and possibly some other characters as well) – Martin Smith Feb 09 '11 at 12:30
1

No problem, I'll take care of the details (the real data is actually numeric, so no funny stuff there). I just needed someone to shoud XML PATH to me :) – SWeko Feb 09 '11 at 12:31

score 5 · Answer 2 · answered Feb 09 '11 at 12:21

5

Have a look at something like

DECLARE @Table TABLE(
        ID INT,
        Value VARCHAR(20)
)
INSERT INTO @Table SELECT 1,'One'
INSERT INTO @Table SELECT 2,'Two'
INSERT INTO @Table SELECT 3,'Three'

SELECT  STUFF(
                (
                    SELECT  ', ' + CAST(ID AS VARCHAR(MAX)) + ': ' + Value
                    FROM    @Table
                    FOR XML PATH(''), TYPE 
               ).value('.','varchar(max)') 
               ,1,2, '' 
          )

answered Feb 09 '11 at 12:21

Adriaan Stander

162,879
31
289
284

This approach is safer than the other two `XML PATH` answers as it deals correctly with XML entitisation but less performant if the data doesn't contain any problematic characters. – Martin Smith Feb 09 '11 at 12:26
@Martin, I assume this is safer because of the `.value...` construct? – Lieven Keersmaekers Feb 09 '11 at 13:53
1

@Lieven - Yes. My remark on performance was based on [Simon Sabin's comment here](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx) "The issue with using the .value is that you invoke the XML Reader operators which aren't the best in terms of performance" – Martin Smith Feb 09 '11 at 13:59

score 3 · Answer 3 · answered Feb 09 '11 at 12:20

3

SELECT STUFF((
    SELECT ' ' + CAST(ID AS VARCHAR(2)) + ': '+ Value
    FROM   dbo.Table
    FOR    XML PATH('')
    ), 1, 1, ''
  ) As concatenated_string

answered Feb 09 '11 at 12:20

Lieven Keersmaekers

57,207
13
112
146

Sparky · Answer 4 · 2011-02-09T12:31:05.830

1

DECLARE @ans VARCHAR(max)

SET @ans=''

SELECT @ans = @ans + str(id)+':'+value FROM table

SELECT @ans

Change the max to 8000 if your version of SQL doesn't support it

edited Feb 09 '11 at 12:31

answered Feb 09 '11 at 12:18

Sparky

14,967
2
31
45

[This isn't guaranteed to work](http://stackoverflow.com/questions/4780513/varcharmax-acting-weird-when-concatenating-string/4780904#4780904) though probably will! – Martin Smith Feb 09 '11 at 12:20
It does work, I've used it for quick table to string functionality. – Sparky Feb 09 '11 at 12:31
SWeko-Not sure what you mean, it doesn't loop the rows. Try it out, the SELECT statement loops the rows and concatenates the fields into the variable – Sparky Feb 09 '11 at 12:34
The edit is ok, this works in a quick and dirty way. Still prefer the XML solution better, though. – SWeko Feb 09 '11 at 13:08
That is the beauty of Stack Overflow, you get multiple answers and can pick the one that is best for you... – Sparky Feb 09 '11 at 16:40

score 0 · Answer 5 · answered Feb 09 '11 at 12:37

I would simply not do this in the database if at all possible. It's not designed for formatting data in a certain way; let the calling application handle that.

In C# (assuming data is an instance of SqlDataReader):

var l = new List<string>();
while (reader.Read())
    l.Add(string.Format("{0}: {1}", reader[0], reader[1]));
var s = string.Join(", ", l.ToArray());

Flatten SQL Server Table to a string

5 Answers5