t-sql 2012 string together names separated by a comma

Question

t-sql 2012 string together names separated by a comma

wendy elizabeth

SSCoach

Points: 16915
More actions
May 1, 2019 at 9:47 pm

#3605986

In t-sql 2012, I need to string together lots of names where the end result where each name will be separated by a comma.
The final endput result will look like the following:
Diane Smith, Ed Clark, James Ragon, Bonnie Fline, Lynn Oster.
The maximum length of the names that will be strung together will varchar(127). If the total length is greater than 127, then show all names up to the maximum length.
**Basically a part of one name might be cut off and that is acceptable.
The basic t-sql to obtain this information will obtain all the applicable names from the same table called customers and the t-sql looks like the following:
declare @vendor int = 5689
select vendors.vendorid, vendors.customerid, customers.firstname, customers.lastname
from vendors
join customers on customers.vendorid= vendors.vendorid
where vendorid = @vendor
There is this requirement since the output will be used on an ssrs 2012 report.
Thus would you show me the t-sql that will accomplish this goal?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24278 More actions · Answer 1

You can use the FOR XML method

declare @vendor int = 5689

select vendors.vendorid, vendors.customerid
     , CustomerNames = CAST(STUFF(( SELECT ',' + customers.firstname + ' ' + customers.lastname
                                    FROM customers
                                    WHERE customers.vendorid = vendors.vendorid
                                    --ORDER BY ...
                                    FOR XML PATH('')
                                   ), 1, 1, '') AS varchar(127))
from vendors
where vendorid = @vendor
group by vendors.vendorid, vendors.customerid

How to post data/code on a forum to get the best help.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

wendy elizabeth

SSCoach

Points: 16915

May 2, 2019 at 3:33 pm

#3629683

Thanks that works!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Now that you have an answer, do you know how it works and why it could fail?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

wendy elizabeth wrote:

There is this requirement since the output will be used on an ssrs 2012 report. Thus would you show me the t-sql that will accomplish this goal?

Consider returning all of the data in the column for the report - and limiting the display to the first 127 characters instead. For that text box - you would set the text box value to an expression and the Tooltip to the full column.

The users will then be able to hover over the text box to see the full results - but the report will only show the first 127 characters.

Another option is to size the text box to the size you want and disable the ability for the text box to grow (or shrink). The text box will then display what it can...and hover over the text box to get the full value.

And finally - you could take that string value separated by commas and convert the commas to vbCrLf in the Tooltip expression. This would then display the list vertically when the user hovers over the textbox instead of horizontally.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs