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

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

  • 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

  • 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 ( '' )), ' ', ', ')

  • Yo can also write example like this:

    DECLARE @MyStatusList VARCHAR(1000)

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

    select @MyStatusList

  • 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

  • In regards distinct values:

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

    GROUP BY field

    works well too.

  • 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 🙂

  • 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

  • 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

  • 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.

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

  • 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)

  • 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.

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

  • 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