Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A simple T-SQL statement to create a list of lookup values Expand / Collapse
Author
Message
Posted Monday, March 21, 2011 3:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 12:12 AM
Points: 13, Visits: 46
One large advantage with XML is that the record count is .. well .. sort of unlimited.
Post #1081139
Posted Monday, March 21, 2011 3:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 21, Visits: 454
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)
Post #1081143
Posted Monday, March 21, 2011 3:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 21, Visits: 128
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.
Post #1081145
Posted Monday, March 21, 2011 4:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:19 AM
Points: 168, Visits: 370
As mentioned above, wouldn't be easier to use COALESCE? You dont't nedd to worry about trailing comma.
Post #1081152
Posted Monday, March 21, 2011 4:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 21, 2012 5:02 AM
Points: 6, Visits: 23
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
Post #1081156
Posted Monday, March 21, 2011 4:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 14, 2011 5:55 AM
Points: 16, Visits: 76
I wrote an article about the problem in 2006. Besides the solution with table function, look at the third solution: http://www.sql-server-performance.com/articles/dba/list_random_values_p1.aspx
Post #1081158
Posted Monday, March 21, 2011 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 21, Visits: 454
Hi Mark, two possible example scenarios:

1) You want to show data in one column that may be held in multiple rows on another table

emp id; emp name; manager(s)
1;Sally; Jack, John, Sarah, Miranda
2;John; Jack
3:Jack; null

2) To send details about in-row drop down lists to a web page where each row needs its own set of drop down items based on some business logic.
Post #1081164
Posted Monday, March 21, 2011 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 21, 2012 5:02 AM
Points: 6, Visits: 23
Hi Liam - perfect, thank you - for me a scenario was all that was missing!

Cheers, Mark
Post #1081166
Posted Monday, March 21, 2011 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 11:53 AM
Points: 2, Visits: 3
See next entry...
Post #1081168
Posted Monday, March 21, 2011 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 11:53 AM
Points: 2, Visits: 3
Hi, somone an idea how to use as subquery it in a view like

DECLARE @mySerials NVARCHAR(max)
SET @mySerials = ''

SELECT OrderNumber, Customer, City,
(
SELECT @mySerials = ISNULL(@mySerials ,'') + Serial + ','
FROM Serials
WHERE Serial.OrderID = Orders.ID
) AS Serials
FROM Orders

The problem seems to be the equal sign near @mySerials =...

Thanks!
Post #1081171
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse