Function to list passenger names?

Question

Post reply

Function to list passenger names?

James Millar-305032

Ten Centuries

Points: 1047
More actions
November 15, 2013 at 12:14 pm

#301280

I need to create a function that lists passenger names separated by a comma. However for the penultimate passenger the comma needs to be replaced with "and" like so:
James, John, Bob, Kate and Julie
Can someone point me in the right direction?
Many thanks,
James
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

CREATE FUNCTION dbo.ListPassengerNames()

RETURNS varchar(8000)

BEGIN

RETURN ('James, John, Bob, Kate, and Julie')

END

Go

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Sorry, I didn't make myself very clear did I...

The passenger list will vary every time in both the names and number of passengers, so I need to somehow find the penultimate passenger and instead of inserting a comma after this passenger name I need to insert "and"

Thanks,

James

Michael L John One Orange Chip Points: 27056 More actions · Answer 3

You're still not very clear. And I couldn't resist the wise guy response...

Is the source of the data rows in a table? A string?, Something else?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 4

basically, you use a FOR XML to build the list, and then replace the LAST comma with an "AND":

here's a working example, with the steps drawn out to help understand the wierdness of the steps, whcih requires REVERSE and STUFF:

With mySampleData(UserName)

AS

(SELECT 'James' UNION ALL

SELECT 'John' UNION ALL

SELECT 'Bob' UNION ALL

SELECT 'Kate' UNION ALL

SELECT 'Julie'

)

SELECT s.Colzs, --the comma delemited list

CHARINDEX(',', REVERSE(s.Colzs)), --where the last comma is,

STUFF(REVERSE(s.Colzs),CHARINDEX(',', REVERSE(s.Colzs)),1,'dna '), --replace the last comma

REVERSE(STUFF(REVERSE(s.Colzs),CHARINDEX(',', REVERSE(s.Colzs)),1,'dna ')) --the final form

FROM(SELECT

Colzs = STUFF((SELECT ', ' + CONVERT(varchar,UserName )

FROM [mySampleData]

ORDER BY UserName

FOR XML PATH('')

),1,2,'') --2 because the ', ' is used.

) s

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 6

Apologies everyone...

Basically I have a table that contains the names that relate to the bookings in the database. I booking can consist of a single passenger or multiple passengers. I need to list the passengers in the booking, which is fine. However I cannot workout how to exchange the comma for the text "and" after the penultimate passenger.

This is what I have so far. I hope this makes sense now...

CREATE FUNCTION dba.spd_ListFolderPax ( @strBBranchCode CHAR(2), @lFFoldNo INT )

RETURNS VARCHAR ( 8000 )

BEGIN

DECLARE @l_strRetVal VARCHAR ( 8000 )

DECLARE @l_strPaxName VARCHAR ( 100 )

DECLARE PaxListCursor CURSOR FAST_FORWARD READ_ONLY LOCAL FOR

SELECT CASE WHEN LastName_FD LIKE '%Unnamed%' THEN

LTRIM( RTRIM( FirstName_FD ))

ELSE

LTRIM( RTRIM( COALESCE( Title_FD, '' ) + ' ' + FirstName_FD + ' ' + LastName_FD ))

END

FROM DBA.Name_TB WITH( NOLOCK )

WHERE BranchCode_FD = @strBBranchCode

AND FolderNo_FD = @lFFoldNo

ORDER BY PaxNo_FD

SELECT @l_strRetVal = ''

OPEN PaxListCursor

FETCH PaxListCursor INTO @l_strPaxName

WHILE @@FETCH_STATUS = 0

BEGIN

IF @l_strRetVal <> ''

BEGIN

SELECT @l_strRetVal = @l_strRetVal + ', '

END

SELECT @l_strRetVal = @l_strRetVal + COALESCE ( @l_strPaxName, '' )

FETCH PaxListCursor INTO @l_strPaxName

END

CLOSE PaxListCursor

RETURN LTRIM( RTRIM( @l_strRetVal ))

END

GO

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

You should look at the excellent code from Lowell. You should not be using a cursor for this...and NOLOCK is most certainly a bad idea here. Do you know all the caveats to that hint? It can be very nasty indeed.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 8

Sorry... as you can see I am no expert!

I believe the the use of no lock is due to the usage of the function that is being called in a Crystal subreport to prevent locking while printing. Ultimately this function will be called in a view that is used to build a Crystal report.

Thanks for your input Lowell, I will take a look and see if I can work it out - pretty funky stuff if you pardon the pun!

Lowell SSC Guru Points: 323518 More actions · Answer 9

dba schema, or was that a misspelled dbo?

here's my code modified to use your table...if you spelled your tables and columns right, i think this will work as advertised, assuming you have clean data without NULLs in any of the three fields you were using..

CREATE FUNCTION dba.spd_ListFolderPax ( @strBBranchCode CHAR(2), @lFFoldNo INT )

RETURNS VARCHAR (max)

AS

BEGIN

DECLARE @l_strRetVal VARCHAR(max);

SELECT @l_strRetVal = --s.Colzs, --the comma delemited list

--CHARINDEX(',', REVERSE(s.Colzs)), --where the last comma is,

--STUFF(REVERSE(s.Colzs),CHARINDEX(',', REVERSE(s.Colzs)),1,'dna '), --replace the last comma

REVERSE(STUFF(REVERSE(s.Colzs),CHARINDEX(',', REVERSE(s.Colzs)),1,'dna ')) --the final form

FROM(SELECT

Colzs = STUFF((SELECT ', ' + CASE

WHEN LastName_FD LIKE '%Unnamed%'

THEN LTRIM( RTRIM( FirstName_FD ))

ELSE LTRIM( RTRIM( COALESCE( Title_FD, '' ) + ' ' + FirstName_FD + ' ' + LastName_FD ))

END

FROM DBA.Name_TB

WHERE BranchCode_FD = @strBBranchCode

AND FolderNo_FD = @lFFoldNo

ORDER BY PaxNo_FD

FOR XML PATH('')

),1,2,'') --2 because the ', ' is used.

) s

RETURN @l_strRetVal

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 10

Wow... I am blown away by the support I receive from you guys! You are one smart cookie Lowell! Thanks for enlightening me on this cold evening 😀

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

I would suggest something much simpler and probably quicker:

With mySampleData(id,UserName)

AS

(SELECT 1,'James' UNION ALL

SELECT 2,'John' UNION ALL

SELECT 3,'Bob' UNION ALL

SELECT 4,'Kate' UNION ALL

SELECT 5,'Julie'

)

select stuff((select case when row_number() over (order by id desc)=1 then ' and ' else ', ' end + UserName

from mySampleData

order by id

for xml path('')),1,2,'')

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 12

Thanks MM, a nice alternative. I have learned a lot about STUFF and FOR XML this week thanks to you guys!