SQL server 12: Displaying column cell values in single cell

[Richard Deeming]

This sort of manipulation is best done in the UI, not the database. However, using the techniques from this blog post[^] leads to the following (rather ugly) solution:

SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

How do you know which first name belongs to which last name?

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Swap9]

I am getting the combined names for the whole table. How to restrict it for a single id? Can you please tell where should I put the condition in the code you have shown above ?

[Richard Deeming]

What do you mean by "a single id"? There's no mention of any column called "id" in your question, or any of the other messages in this thread.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Swap9]

ID Column1 Column2 -- -------- ---------- 7 Vaugh William 7 Vaugh Smith 6 Woods Jane 6 Woods Joseph 6 Wright Adam 6 Wright John Suppose I have to find the combined name only for id=6 , then where should I put the condition in the code? The expected output : Woods,Jane-Joseph/Wright,Adam-John

[Richard Deeming]

So the ID is the same for each surname? In that case, you just need to add a WHERE clause between the FROM YourTable As T1 and GROUP BY Surname lines:

SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM YourTable As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM YourTable As T1

    -- Filter here:
    WHERE T1.ID = @TheIDToFind
    
    GROUP BY Surname
    ORDER BY Surname
    FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames

;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Swap9]

ID Column1 Column2 -- -------- ---------- 7 Vaugh William 7 Vaugh Smith 6 Woods Jane 6 Woods Joseph 6 Wright Adam 6 Wright John Thanks, I exactly put the condition at the place you suggeted, but still the output I get is as below WRONG OP : Vaugh,William-Smith/Woods,Jane-Joseph/Wright, Adam-John Expected OP : Woods,Jane-Joseph/Wright, Adam-John

[Richard Deeming]

I've just tried the code here on your sample data, and I get the expected output.

DECLARE @T TABLE
(
ID int NOT NULL,
Surname varchar(10) NOT NULL,
Forename varchar(10) NOT NULL
);

INSERT INTO @T (ID, Surname, Forename)
VALUES
(7, 'Vaugh', 'William'),
(7, 'Vaugh', 'Smith'),
(6, 'Woods', 'Jane'),
(6, 'Woods', 'Joseph'),
(6, 'Wright', 'Adam'),
(6, 'Wright', 'John')
;

SELECT
STUFF(
(SELECT '/' + Surname + ',' + STUFF(
(SELECT '-' + Forename
FROM @T As T2
WHERE T2.Surname = T1.Surname
ORDER BY T2.Forename
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM @T As T1
WHERE T1.ID = 6
GROUP BY Surname
ORDER BY Surname
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As CombinedNames
;

-- Output:
-- Woods,Jane-Joseph/Wright,Adam-John

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Richard Deeming]

Here's a SQL Fiddle with the same query, which also generates the correct output: http://sqlfiddle.com/#!3/300af/1[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Swap9]

I had to add few extra condition suitable to my table, but it DID WORK !! :) I have learnt about STUFF and FOR XML PATH. Thanks a ton for all your time Richard.