SQL server 12: Displaying column cell values in single cell

Swap9

Column1 Column2 -------- ---------- Vaugh William Vaugh Smith Woods Jane Expected Output : Vaugh, William-Smith/Woods,Jane Condition : There could be n number of rows in the sample table, there could be n number of woods too. display all the common surname having different names meaning repeated Last names will be once then all the first names associated with it. truncated at 30 characters max. How can I get this output in SQL query ?

ZurdoDev

This is not clear.

There are only 10 types of people in the world, those who understand binary and those who don't.

Swap9

What extra information will make it clear ?

ZurdoDev

It looks like you want a single record as your output? What are the conditions?

There are only 10 types of people in the world, those who understand binary and those who don't.

Swap9

yes there is just single cell output. but conditions are that cell can not have more than 30 characters. Even there are 10 or 15 rows in a table, the SQL query should give single cell output.

ZurdoDev

That doesn't make any sense. You want to concatenate everything into a single value? If so you can do similar to:

DECLARE @temp NVARCHAR(MAX)
SELECT @temp = COALESCE(@temp + ', ', '') + field1
FROM table

There are only 10 types of people in the world, those who understand binary and those who don't.

Swap9

I want to display the unique last names followed by their respective first names separated by a '-' and, then '/' , then again the next last name(If it is not distinct , then add their first names separated by '-')followed by its respective firstname and so on..

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.