Pivoting row data into a single column

Question

I have the following:

BEGIN TRANSACTION

CREATE TABLE #temp
(
    testing VARCHAR(MAX)
)
INSERT INTO #temp
  ( testing )
VALUES ('Boomerang')
INSERT INTO #temp
  ( testing )
VALUES ('Bowling')
INSERT INTO #temp
  ( testing )
VALUES ('Boxing')
INSERT INTO #temp
  ( testing )
VALUES ('Bull Fighting')


SELECT *
FROM #temp

ROLLBACK TRANSACTION

And I'm trying to display it as a single column result like the following:

Boomerang|Bowling|Boxing|Bull Fighting

However I've no idea how to do this. I've looked into Pivot but it doesn't seem to address my issue.

http://stackoverflow.com/a/17591536/335858 – Sergey Kalinichenko Dec 09 '13 at 10:48 — Sergey Kalinichenko, Dec 09 '13 at 10:48

score 3 · Accepted Answer · answered Dec 09 '13 at 10:55

This is another way of doing it using string concatenation with Isnull() [or coalesce()] function.

Fiddle demo:

declare @str varchar(max)

Select @str = isnull(@str + '|', '') + testing
From temp
Order by testing

Select @str
--Results
Boomerang|Bowling|Boxing|Bull Fighting

peterm · Answer 2 · 2013-12-09T11:14:31.417

2

SELECT STUFF(
        (
          SELECT '|' + testing
            FROM #temp
             FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') list

Output:

| LIST                                   |
|----------------------------------------|
| Boomerang|Bowling|Boxing|Bull Fighting |

Here is SQLFiddle demo

edited Dec 09 '13 at 11:14

answered Dec 09 '13 at 10:49

peterm

88,818
14
143
153

Did it help? Do you need more help with your question? – peterm Dec 10 '13 at 02:37

Pivoting row data into a single column

2 Answers2