Select out rows into one result - Similar Problem

  • I agree that formatting is the job of the presentation layer, but aggregating data may not be. While the original question did not say, the data may be used in reports, and further aggregated for statistics to populate other controls.

    It would take significantly less code to do this in SQL server, than it would to do that in asp,vb,.net or whatever. I would go so far as to say the logical IO would probably be less too. Besides, SQL Server has inherent caching propensities that we can take advantage of.

    I think doing this in SQL Server is ideal, therefore.

     

    All my love,

    Michael

    "My other computer is your Windows box"

     

     

  • Sorry, all, but me thinks that some of you

    are making this much too hard.

    -- Create this function

    -- It will return ALL colors for each item

    -- Change sizes to more appropriate as necessary

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    ALTER FUNCTION dbo.ufx_getColors

    @item varchar(3)

    -- print dbo.ufx_getColors('361')  <-- Used to test function

    RETURNS VARCHAR(1000) AS 

    BEGIN

    DECLARE @colorList varchar(1000)

    BEGIN

    SELECT @colorList = COALESCE(@colorList + '|', '') +

       CAST(LTrim(RTrim(Color)) AS varchar(80))

    FROM

     [<database>].[dbo].[<tableName>]

    WHERE

     item = @item

    END

    RETURN @colorList

    END

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    -- Use this in an SP to select

    -- All Colors for All Items

    SELECT

     item,

     dbo.ufx_getColors(item) As Colors

    FROM

     dbo.<tableName>

    Where

     dbo.ufx_getColors(item) is not null

    Hope this helps.

     


    Butch

  • Hi all,  Thanks for your responses.  They have been very helpful.

    A few clarifications.

    1) Each item has a variable number of colors, and you don't know the # of colors beforehand

    2) This is for data processing logic, and can't be solved by the presenation tier only.

    3) I'm on SQL Server 7.0.  However the function solutions will be very handy when we move to 2000.

    Here is what I came up with.

    SET NOCOUNT ON

    SET CONCAT_NULL_YIELDS_NULL OFF

    DECLARE @ITEM AS varchar(12)

    SET @ITEM = '361'

    CREATE TABLE #ROWNUM

    (

    ROWNUMBER INT IDENTITY(1,1),

    ITEM VARCHAR(12),

    COLOR VARCHAR(12)

    )

    INSERT #ROWNUM (ITEM, COLOR)

    select distinct item, color from sku

    where item = @ITEM

    order by color

    DECLARE @CTR INT,

    @max-2 INT,

    @LIST VARCHAR(255)

    SET @CTR = 1

    SET @max-2 = (select MAX(ROWNUMBER) from #ROWNUM)

    WHILE @CTR <= @max-2

    BEGIN

    --PRINT 'COUNTER IN: ' + CAST(@CTR AS CHAR) --Used for Debug

    SET @LIST = @LIST + '|' + RTRIM((SELECT COLOR FROM #ROWNUM WHERE ROWNUMBER = @CTR))

    --SET @LIST = (SELECT COLOR FROM #ROWNUM WHERE ROWNUMBER = @CTR) --Used for Debug

    --PRINT 'LIST: ' + @LIST --Used for Debug

    SET @CTR = @CTR + 1

    --PRINT 'COUNTER OUT: ' + CAST(@CTR AS CHAR) --Used for Debug

    --PRINT '*************'

    END

    SET @LIST = SUBSTRING(@LIST, 2, LEN(@LIST) - 1) --strip leading '|'

    --SELECT * FROM #ROWNUM --Used for Debug

    --SELECT @max-2 --Used for Debug

    SELECT @LIST

    DROP TABLE #ROWNUM

     

    What do you all think?

  • Hi jgljgl,

    Given the facts that the number of colors is variable and unknown and that you'll select only one item at a time, I think that xnl28's solution does exactly what you want and is, as xnl28 says himself, "simple and elegant".

    Cheers,

    Henk

Viewing 4 posts - 16 through 18 (of 18 total)

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