August 27, 2004 at 10:44 pm
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"
August 28, 2004 at 11:49 am
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
August 30, 2004 at 2:56 pm
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?
August 31, 2004 at 1:22 am
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