2

Imagine we have a table as:

AAA BBB 123
AAA BBB 456
AAA BBB 789

How can i see these three in a single row as:

AAA BBB 123-456-789 (multiple cells are added to a single cell using a delimiter)

Cheers.

Sin5k4
  • 1,556
  • 7
  • 33
  • 57
  • search for group_concat workaround: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – dani herrera Jun 11 '13 at 10:45

2 Answers2

1

Try this one -

Query:

DECLARE @temp TABLE
(
      a VARCHAR(10)
    , b VARCHAR(10)
    , c INT
)

INSERT INTO @temp (a, b, c)
VALUES 
    ('AAA', 'BBB', 123),
    ('AAA', 'BBB', 456),
    ('AAA', 'BBB', 789)

SELECT t.a + ' ' + t.b + STUFF((
    SELECT '-' + CAST(c AS VARCHAR(10))
    FROM @temp t2
    WHERE t2.a = t.a
        AND t2.b = t.b
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ' ')
FROM (
    SELECT DISTINCT a, b 
    FROM @temp
) t

Output:

AAA BBB 123-456-789
Devart
  • 119,203
  • 23
  • 166
  • 186
0

Using the answer found here:

WITH X AS 
(
    SELECT 'AAA' AS A, 'BBB' AS B, 123 AS C
    UNION
    SELECT 'AAA' AS A, 'BBB' AS B, 456 AS C
    UNION
    SELECT 'AAA' AS A, 'BBB' AS B, 789 AS C
)
SELECT DISTINCT
    X.A,
    X.B,
    STUFF((SELECT '-' + CAST(C AS VARCHAR(3)) FROM X FOR XML PATH ('')), 1, 1, '')
FROM
    X
Community
  • 1
  • 1
robyaw
  • 2,274
  • 2
  • 22
  • 29