Post reply

Need to return a SELECTed list from another table into one row of a SELECT statement

  • July 13, 2012 at 8:22 am

    #392739

    I have a need in a s/p to SELECT one concatenated name value that is a list of 2-4 persons' first and last names, separated by commas. I have to SELECT the list of those people's names from another table, something like this:

    SELECT [datavalue1], [datavalue2], (firstname1 + ' ' + lastname1 + ', ' + firstname2 + ' ' + lastname2) AS MortgagorNames ...

    Currently I'm trying to write a function to return the name list that I could call from my s/p as in:

    "..., SELECT dbo.ufnMortgagorNames(LoanID) AS MortgagorNames, ..."

    It doesn't work--I get no errors or messages, but the function doesn't return any value.

    1. I may be spinning my wheels here. Is it even possible to write a function that contains a CURSOR? I haven't found anything in BOL that definitively says yes or no.

    2. I know this solution is ugly as sin, but can't think of a more elegant way to do this. Any other ideas out there?

    Thanks in advance,

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • July 13, 2012 at 8:28 am

    #1512569

    there's a neat trick using FOR XML you can use to concatinate rows into a delimited list.

    is this what you are looking for?

    SELECT DISTINCT

    t.name,

    sq.Columns

    FROM sys.tables t

    JOIN (

    SELECT OBJECT_ID,

    Columns = STUFF((SELECT ',' + name

    FROM sys.columns sc

    WHERE sc.object_id = s.object_id

    FOR XML PATH('')),1,1,'')

    FROM sys.columns s

    ) sq ON t.object_id = sq.object_id

    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!

  • July 13, 2012 at 9:24 am

    #1512616

    Lowell is spot on the with the FOR XML way.

    I think your original "no value" is possibly because you are concatenating strings and not accounting for NULL. In other words you are adding several varchar values together and if ANY of them are NULL the entire result is NULL.

    select 'asdf' + NULL

    _______________________________________________________________

    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/

  • July 13, 2012 at 9:51 am

    #1512631

    Sean, you're right, I was pulling in some NULLs and thereby smashing the result.

    Lowell, I'm still deconstructing your example--it's over my head at this point. But I'll get it.

    Thanks for the help, guys.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

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

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