A simple T-SQL statement to create a list of lookup values

  • Suresh Kumar-365787

    SSC Enthusiast

    Points: 197

    Comments posted to this topic are about the item A simple T-SQL statement to create a list of lookup values

  • Mansfield

    SSCrazy

    Points: 2366

    You can use the following SQL to compose the comma-delimited string, without needed to trim the trailing comma at the end:

    SELECT @MyStatusList = ISNULL(@MyStatusList + ',', '') + StatusDesc FROM (SELECT DISTINCT StatusDesc FROM MyStatus) x

  • feeza_ibrahim

    Valued Member

    Points: 63

    It can be made simple with the following sql statement

    SELECT REPLACE((SELECT DISTINCT RTRIM(LTRIM(StatusDesc)) AS 'data()'

    FROM dbo.MyStatus

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

  • Jozef Cepel

    SSC Rookie

    Points: 41

    Yo can also write example like this:

    DECLARE @MyStatusList VARCHAR(1000)

    SELECT @MyStatusList = COALESCE(@MyStatusList + ',', '') + StatusDesc FROM MyStatus

    select @MyStatusList

  • Jason-299789

    SSC-Insane

    Points: 21601

    Cursors in MS T-SQL should be avoided unless absolutely necessary as they are extremely slow and inefficent.

    Using one of the methods mentioned in previous posts is a better and more efficent alternative to the method you mention, my personal preference is the XML conversion with a nested Replace.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • arty 15255

    Old Hand

    Points: 341

    In regards distinct values:

    SELECT @S = ISNULL(@s+',','')+field FROM dbo.TABLE

    GROUP BY field

    works well too.

  • arty 15255

    Old Hand

    Points: 341

    arty 15255 (3/21/2011)


    In regards distinct values:

    SELECT @S = ISNULL(@s+',','')+field FROM dbo.TABLE

    GROUP BY field

    works well too.

    I forgot to mention that ORDER BY field works as well 🙂

  • hrc

    Old Hand

    Points: 323

    Jason: May I ask for a a short example on how to do XML with nested replaces? I have no experience with XML in SQL. Okay. Found it, don't understand it though. 5 minutes later I understand it. A better solution because there's no record limit

  • hrc

    Old Hand

    Points: 323

    Jason: May I ask for a a short example on how to do XML with nested replaces? I have no experience with XML in SQL. Okay. Found it, don't understand it though

  • liam.gavin

    Ten Centuries

    Points: 1297

    Jason, the method described does not use a cursor. It is good to share the method in the article but it should be known and in use already which judging by posts it is. Comparing the execution plan for XML version and article version shows SQL handling the query in exactly the same way, so no benefit from using XML in this instance that I can see. Might be worthwhile exploring XML though if requirements were more complex.

  • hrc

    Old Hand

    Points: 323

    One large advantage with XML is that the record count is .. well .. sort of unlimited.

  • liam.gavin

    Ten Centuries

    Points: 1297

    That would definitely come in useful when extracting data from SQL, each data item could be 1000 characters in which case you would only need 8 rows before you start seeing truncation or have to start using varchar(max)

  • stefan.hoffmann

    SSC Journeyman

    Points: 99

    hi,

    I would either use one of both solutions:

    DECLARE @Concat NVARCHAR(MAX) = '' ;

    SELECT @Concat = @Concat + ', ' + StatusDesc

    FROM MyStatus ;

    SELECT STUFF(@Concat, 1, 2, '') ;

    -- or

    DECLARE @Concat2 NVARCHAR(MAX) ;

    SELECT @Concat2 = COALESCE(@Concat2 + ', ', '') + StatusDesc

    FROM MyStatus ;

    SELECT @Concat2 ;

    The use of COALESCE is interchangeable with ISNULL in this case. The use of SUBSTRING and LEN is not necessary when you use STUFF. Also I would not limit the length of the result variable to any arbitrary value.

    The FOR XML PATH approach is in this case also viable when I only want to issue one single command or when we need to concatenate values in groups.

  • vevoda.ulath

    Mr or Mrs. 500

    Points: 592

    As mentioned above, wouldn't be easier to use COALESCE? You dont't nedd to worry about trailing comma.

  • mark.tait

    SSC-Addicted

    Points: 478

    Hi - can I ask for a scenario for when this would be useful? I'm keen to learn.

    As I see it, if I wanted a list of values - the context I would use it in, wouls be a select statement - so why not just do this:

    Select myID, myField from myTable where myDesc in (Select Descriptions from DescTable)

    Thanks for any clarification.

    Mark

Viewing 15 posts - 1 through 15 (of 88 total)

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