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.
-
search for group_concat workaround: stackoverflow.com/questions/273238/…dani herrera– dani herrera2013-06-11 10:45:35 +00:00Commented Jun 11, 2013 at 10:45
Add a comment
|
2 Answers
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
Comments
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
