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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy