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

Question

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

Suresh Kumar-365787

SSC Enthusiast

Points: 197
More actions
March 20, 2011 at 9:12 pm

#389089

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

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

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

Mansfield SSCrazy Points: 2396 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

In regards distinct values:

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

GROUP BY field

works well too.

arty 15255 Old Hand Points: 341 More actions · Answer 6

arty 15255 (3/21/2011)
In regards distinct values:
SELECT @s-2 = 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: 333 More actions · Answer 7

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: 333 More actions · Answer 8

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 More actions · Answer 9

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: 333 More actions · Answer 10

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

liam.gavin Ten Centuries Points: 1297 More actions · Answer 11

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 More actions · Answer 12

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 SSChasing Mays Points: 622 More actions · Answer 13

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 More actions · Answer 14

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