April 5, 2007 at 9:43 am
I have a table Call Employee ! and bellow is the Table data
Total- (data Type –Decimal)
263.07
458.02
45.58
How to display this information like this ? in to a single result !
Summary
263.07, 458.02, 45.58
cheers
April 5, 2007 at 10:00 am
Personally i'd make the presentation layer do it.
But you can use a UDF to do the same thing.
-- Set up Test Scenario
use pubs
Create table mytable (pk int identity, [ID] int, col varchar(20))
insert into mytable ([id],col)
select 1, 'aaa' union
select 2, 'bbb' union
select 3, 'ccc' union
select 3, 'ddd' union
select 4, 'eee' union
select 5, 'fff' union
select 5, 'ggg' union
select 6, 'hhh'
-- =============================================
-- Create scalar function (FN)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'fn_ConcatStrings')
DROP FUNCTION fn_ConcatStrings
GO
CREATE FUNCTION fn_ConcatStrings
(@ID int)
RETURNS varchar(500)
AS
BEGIN
declare @String varchar(500)
select @String = coalesce(@String,'') + Col + ','
from mytable
where [Id] = @ID
if Right(@String,1) = ','
set @String = substring(@String,1,len(@String) -1)
return @String
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT [id], dbo.fn_ConcatStrings ([id])
from mytable
where pk = 3
April 5, 2007 at 11:32 am
create table empl(total decimal(5,2))
go
insert into empl
select 263.07 union
select 458.02 union
select 45.58
go
select
stuff
(
(
select ','+ cast(total as varchar(6)) from empl t1
for xml path('')
)
,1,1,''
)
go
April 5, 2007 at 11:41 am
I posted similar question. And I got answer from these guys.
Check this out
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply