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

  • 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

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

  • Hi Liam - perfect, thank you - for me a scenario was all that was missing!

    Cheers, Mark

  • See next entry...

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

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

  • You can not use this in VIEW. Use XML approach in VIEW.

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

  • Ofcourse better we can make a as UDF...Wont it?

  • SELECT STUFF((

    SELECT ',' + rtrim([StatusDesc])

    FROM [dbo].[MyStatus]

    ORDER BY [StatusDesc]

    FOR XML PATH(''))

    ,1,1,'')

  • 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

  • // Do it in one step,

    DECLARE @status varchar(max)

    SELECT @status =

    CASE WHEN @status is null

    THEN StatusDesc

    ELSE @status + ', ' + + StatusDesc

    END

    FROM mystatus

    SELECT @status

    /*

    returns sans trailing comma

    Active, OnHold, Disabled, Closed

    */

  • mmm...impresive...nice solution

  • Would someone please provide a brief explanation as to why these solutions work? Is it just simple recursion?

  • You can also do this with two variables with one SELECT:

    DECLARE @t varchar(max)

    DECLARE @S varchar(1)

    SET @t = ''

    SET @S = ''

    SELECT @t += @S + Tag, @S = ',' 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