March 8, 2010 at 4:04 pm
Hello,
I am trying to create a function that will produce an avg() for the selected results based on a string containing the digit of the filed ie(rda1).
the String I have to work with contains something like this '1,4,5,9,14'
the number in the string relfects which rdaX
so I need a function that will take the value in rda1,rda4,rda5,rda9 and rda14 in this example for every line in the z3 table which in reality is 86 and return the avg for each line so my results contain something like this:
intnum avgrda
1 avg(1050+1167+1167+1397+1517) as avgrda
2 avg(23+26+30+35+40) as avgrda
Below is what my table looks like and insert sample data
CREATE TABLE [dbo].[z3](
[intnum] [smallint] ,
[id] [nvarchar](64),
[abbreviation] [nvarchar](8) ,
[usda_no] [nvarchar](8) ,
[unit] [smallint] ,
[rda1] [real] ,
[rda2] [real] ,
[rda3] [real] ,
[rda4] [real] ,
[rda5] [real] ,
[rda6] [real] ,
[rda7] [real] ,
[rda8] [real] ,
[rda9] [real] ,
[rda10] [real],
[rda11] [real],
[rda12] [real],
[rda13] [real],
[rda14] [real])
INSERT INTO [dbo].[z3]([intnum],[id]
,[abbreviation]
,[usda_no]
,[unit]
,[rda1]
,[rda2]
,[rda3]
,[rda4]
,[rda5]
,[rda6]
,[rda7]
,[rda8]
,[rda9]
,[rda10]
,[rda11]
,[rda12]
,[rda13]
,[rda14]
)
VALUES
(1
,'Calories (kcal))'
,'cal'
,208
,1
,758
,1050
,1050
,1050
,1167
,1167
,1167
,1167
,1397
,1397
,1397
,1397
,1517
,1517
)
(2
,'Vitamin C'
,'vitC'
,401
,1
,23
,26
,26
,26
,30
,30
,30
,30
,35
,35
,35
,35
,40
,40
)
GO
March 8, 2010 at 7:20 pm
I believe that I understand what you want but you could please be a little more specific?
Regards,
W.C.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 8, 2010 at 7:45 pm
I agree with Welsh, I couldn't understand what you were asking us for.
Also, the design of your tables looks very non-relational. "No repeating columns" is a very important relational rule and violating it like this is bound to cause you problems.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 9:02 pm
jrgustin 30365 (3/8/2010)
Hello,I am trying to create a function that will produce an avg() for the selected results based on a string containing the digit of the filed ie(rda1).
the String I have to work with contains something like this '1,4,5,9,14'
the number in the string relfects which rdaX
so I need a function that will take the value in rda1,rda4,rda5,rda9 and rda14 in this example for every line in the z3 table which in reality is 86 and return the avg for each line so my results contain something like this:
intnum avgrda
1 avg(1050+1167+1167+1397+1517) as avgrda
2 avg(23+26+30+35+40) as avgrda
Below is what my table looks like and insert sample data
CREATE TABLE [dbo].[z3](
[intnum] [smallint] ,
[id] [nvarchar](64),
[abbreviation] [nvarchar](8) ,
[usda_no] [nvarchar](8) ,
[unit] [smallint] ,
[rda1] [real] ,
[rda2] [real] ,
[rda3] [real] ,
[rda4] [real] ,
[rda5] [real] ,
[rda6] [real] ,
[rda7] [real] ,
[rda8] [real] ,
[rda9] [real] ,
[rda10] [real],
[rda11] [real],
[rda12] [real],
[rda13] [real],
[rda14] [real])
INSERT INTO [dbo].[z3]([intnum],[id]
,[abbreviation]
,[usda_no]
,[unit]
,[rda1]
,[rda2]
,[rda3]
,[rda4]
,[rda5]
,[rda6]
,[rda7]
,[rda8]
,[rda9]
,[rda10]
,[rda11]
,[rda12]
,[rda13]
,[rda14]
)
VALUES
(1
,'Calories (kcal))'
,'cal'
,208
,1
,758
,1050
,1050
,1050
,1167
,1167
,1167
,1167
,1397
,1397
,1397
,1397
,1517
,1517
)
(2
,'Vitamin C'
,'vitC'
,401
,1
,23
,26
,26
,26
,30
,30
,30
,30
,35
,35
,35
,35
,40
,40
)
GO
Nice post. Inclusion of the readily consumable data, like you did, makes it easy to figure out what you want. As a sidebar, though, I'd stop using the muliple value VALUES statement on your posts because not all of the people that can help you all have 2k8.
First, you need a function to split your input parameter. The following will do in most cases...
-- drop function dbo.DelimitedSplit8K --Careful what you drop ;-)
go
CREATE FUNCTION dbo.DelimitedSplit8K
/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list
of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Does not "trim" elements just in case leading or trailing
blanks are intended.
4. cteTally concept originally by Iztek Ben Gan and
"decimalized" by Lynn Pettis (and others) for a bit of
extra speed and finally redacted by Jeff Moden for a
different slant on readability and compactness.
5. If you don't know how a Tally table can be used to replace
loops, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Final redaction and test.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
- See Gianluca Sartori's solution on the following URL
http://ask.sqlservercentral.com/questions/4241/whats-the-best-way-to-solve-the-fizzbuzz-question
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
--===== "Inline" CTE Driven "Tally Table” produces values up to
-- 10,000... enough to cover VARCHAR(8000)
WITH
E1(N) AS ( --=== Create Ten 1's very quickly
SELECT N
FROM (SELECT 1 AS N0, 1 AS N1, 1 AS N2, 1 AS N3, 1 AS N4,
1 AS N5, 1 AS N6, 1 AS N7, 1 AS N8, 1 AS N9) AS E0
UNPIVOT (N FOR EN IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt
),--10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
N AS StartPosition,
SUBSTRING(@pDelimiter + @pString,
N+1,
CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
Then, using your good data, the rest is cake...
--===== Here's you input parameter
DECLARE @pRDA_X VARCHAR(8000);
SELECT @pRDA_X = '1,4,5,9,14';
--===== Here's the solution to your problem
WITH
cteRDA AS
( --=== This CTE does an UNPIVOT to "normalize" the data
SELECT IntNum, RDA_Col, SUBSTRING(RDA_Col,4,8000) AS RDA_X, RDA_Val
FROM ( --=== Indentify the PK and the columns to be unpivoted
SELECT IntNum, RDA1, RDA2, RDA3, RDA4, RDA5, RDA6, RDA7, RDA8, RDA9, RDA10, RDA11, RDA12, RDA13, RDA14
FROM dbo.Z3
) src
UNPIVOT ( --==== Do the unpivot
RDA_Val FOR RDA_Col
IN (RDA1, RDA2, RDA3, RDA4, RDA5, RDA6, RDA7, RDA8, RDA9, RDA10, RDA11, RDA12, RDA13, RDA14)
) AS unpvt
) --=== Using the above "normalized" data and the "split" function, finding the averages you asked for
-- becomes very simple.
SELECT rda.IntNum, AVG(rda.RDA_Val) AS AverageRDA
FROM cteRDA rda
INNER JOIN dbo.DelimitedSplit8K(@pRDA_X, ',') split
ON rda.RDA_X = split.Item
GROUP BY rda.IntNum;
As yet another sidebar and in full agreement with what Barry said, I'd strongly urge you to normalize the table in a manner similar to what the CTE in the code above does. The table is called an EAV (Entity, Attribute, Value) table. Some folks don't like them but I think it's an appropriate use here because it's sure not normalized the way you have it and you'll continue to struggle to use it for these types of problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2010 at 9:22 pm
Heh, well some of us are slower than others. 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 9:40 pm
Nah... I was "Jonesin' " for a code problem instead of all the bloody performance problems we've had lately. Heh... I was on a "mission" on this one especially since the OP made it easy with some readily consumable data. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2010 at 9:55 am
Thanks a lot that works great. I did forgot to add the function I use for parsing the string and I did leave out some of the essential table values but using this function does exactly what i was looking for. I will make sure next time to include those things next time. 🙂
March 9, 2010 at 11:54 am
Some outstanding code! 😎
But please take the advice & normalize your tables.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 10, 2010 at 4:04 pm
jrgustin 30365 (3/9/2010)
Thanks a lot that works great. I did forgot to add the function I use for parsing the string and I did leave out some of the essential table values but using this function does exactly what i was looking for. I will make sure next time to include those things next time. 🙂
You might want to post your parsing function... not that you did but some folks have picked up some pretty bad parsing functions from the Internet. The Ninjas on this forum would probably be happy to take a look-see for you and provide a "tune-up" or alternate (like I did in my previous post) if they see a potential performance problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2010 at 4:06 pm
Welsh Corgi (3/9/2010)
Some outstanding code! 😎
Thanks, WC! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2010 at 8:36 am
Thanks for the feedback and here the the parsing function that I use for a lot of my SSR reporting adn many other things. I ended up having to modify this function for one more thing in order to get the function you gave me working correctly with the data I was working with.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Param]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(10)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS INT))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
GO
and this is the one I had to use in order to add another number to each of the values of the string( some bad coding to begin with ) but it works now
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_ParamRDA]
(@RepParam nvarchar(4000), @Delim char(1)= ',',@RDAGrp real)
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(10)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS INT)+@RDAGrp)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
GO
Hope this might help anyone also.
March 11, 2010 at 3:52 pm
jrgustin 30365 (3/11/2010)
I ended up having to modify this function for one more thing in order to get the function you gave me working correctly with the data I was working with.
Can I see those modifications as well?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2010 at 3:57 pm
I was just refering the my Param Function , I had to add another input to it in order to accomondate for which meal plan they were selecting and add a it to each value in the string then send that to your function. I didn't modify anything you wrote since it worked great.
March 11, 2010 at 9:20 pm
Ah... got it.
Did you modify the two functions you posted to get rid of the While Loops or do you still need some help there?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply