March 21, 2011 at 4:31 am
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
March 21, 2011 at 4:38 am
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.
March 21, 2011 at 4:42 am
Hi Liam - perfect, thank you - for me a scenario was all that was missing!
Cheers, Mark
March 21, 2011 at 4:50 am
See next entry...
March 21, 2011 at 4:56 am
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!
March 21, 2011 at 5:01 am
DECLARE @EmployeeIDCSV varchar(100)
SELECT @EmployeeIDCSV = COALESCE(@EmployeeIDCSV+ ', ', '') + CAST(Emp_ID AS varchar(5)) FROM Employees
SELECT @EmployeeIDCSV
We can also use this...So compact...
March 21, 2011 at 5:02 am
You can not use this in VIEW. Use XML approach in VIEW.
March 21, 2011 at 5:10 am
You can use this method in a view but you need to put your lookup query into a function (unique id as input, returns varchar) then use the function in line specifying unique id.
XML maybe easier and more efficient for this.
March 21, 2011 at 5:14 am
Ofcourse better we can make a as UDF...Wont it?
March 21, 2011 at 5:32 am
SELECT STUFF((
SELECT ',' + rtrim([StatusDesc])
FROM [dbo].[MyStatus]
ORDER BY [StatusDesc]
FOR XML PATH(''))
,1,1,'')
March 21, 2011 at 5:43 am
ws-581925 (3/21/2011)
SELECT STUFF((SELECT ',' + rtrim([StatusDesc])
FROM [dbo].[MyStatus]
ORDER BY [StatusDesc]
FOR XML PATH(''))
,1,1,'')
Could help with distinct here ...if you use order as well
March 21, 2011 at 6:02 am
mmm...impresive...nice solution
March 21, 2011 at 6:11 am
Would someone please provide a brief explanation as to why these solutions work? Is it just simple recursion?
March 21, 2011 at 6:27 am
You can also do this with two variables with one SELECT:
DECLARE @t varchar(max)
DECLARE @s-2 varchar(1)
SET @t = ''
SET @s-2 = ''
SELECT @t += @s-2 + Tag, @s-2 = ',' FROM Tags ORDER BY Tag
SELECT @t
And just for fun, a recursive CTE solution! :hehe:
DECLARE @t varchar(max);
SET @t = '';
WITH rectags AS
(
SELECT TOP 1 Tag FROM Tags ORDER BY Tag
UNION ALL
(
SELECT ', ' + Tag FROM Tags
EXCEPT
SELECT TOP 1 ', ' + Tag FROM Tags ORDER BY Tag
)
)
SELECT @t += Tag FROM rectags
SELECT @t
Viewing 15 posts - 16 through 30 (of 87 total)
You must be logged in to reply to this topic. Login to reply