Can I create a floating computed value in a list of records
Dave,
This has been bugging me, and I think I have a more elegant solution... This script is more dynamic, and I think that it should fully meet your needs. Let me know how you get on with it.
DECLARE @values table(col1 float)
DECLARE @values2 table(col1 float)
INSERT INTO @values VALUES(2), (2), (3), (4), (5)
INSERT INTO @values2
SELECT AVG(col1) AS col1 FROM @values
UNION ALL
SELECT col1 FROM @values
ORDER BY col1
SELECT DISTINCT ConCat_Column =
(
SELECT CAST(col1 as decimal(3,2)) AS [data()]
FROM @values2
FOR XML PATH ('')
)
Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
March 13th, 2011 11:39am
I am displaying numeric record sets representing individuals’ attainments for a period of time. The records can be filtered by
time period, monthly, quarterly, yearly and by areas, regions, states, cities, etc. Based on the query selection, can an average value be computed and then displayed in the record set?
So the records returned might be:
2, 2, 3, 4, 5 so the average is 3.2.
So I want the display to return 2, 2, 3, 3.2, 4, 5.
Using this I would be able to show the individuals were they are stack ranked relative to the average. Doing so would allow the average
to change depending on the records returned.
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2011 12:29pm
Hi Dave,
Try something like this. You may need to tinker with the start parameter of the stuff function to meet your needs. I have it hard coded at the moment, but it should be a good starting point for you...
declare
@values
table(col1
float)
insert
into
@values
values(2),
(2),
(3),
(4),
(5)
select
distinct
ConCat_Column
=
STUFF
((SELECT
cast(col1
as
int)
AS
[data()]
FROM
@values
FOR
XML
PATH
('')
),
6,
0,
' '
+
(SELECT
CAST(AVG(Col1)
as
varchar(5))
from
@values)
+
' ')
from
@values
Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
March 13th, 2011 12:30pm
Forgive my formatting... This again...
declare @values table(col1 float)
insert into @values values(2), (2), (3), (4), (5)
select distinct ConCat_Column =
STUFF((SELECT cast(col1 as int) AS [data()] FROM @values FOR XML PATH ('') ), 6, 0, ' ' + (SELECT CAST(AVG(Col1) as varchar(5)) from @values) + ' ')
from @valuesPeter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2011 12:31pm
Thanks for the efforts!
I will work on it today and let you know.
Dave
March 13th, 2011 12:51pm
This topic is archived. No further replies will be accepted.
Other recent topics
