T SQL Concatenated Row Field

When creating new reports you sometimes need to concatenate data:

 Below is sample script which will allow you to do this using a function.



----------------------------------------------------------------------------
------ create table
----------------------------------------------------------------------------
with cte as (select 123 as customer, '2012-09-18 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-19 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-20 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-21 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-22 00:00:00.000' as orderdate
)

select * into CustomerOrder from cte




----------------------------------------------------------------------------
------ create function to concatenate dates
----------------------------------------------------------------------------
create    function [dbo].[GetCustomerOrderDates](
@customer varchar(20)

)
returns varchar (8000)
as
begin
declare @str varchar (8000)
  set @str = ''
 
if @customer is null
  return ''



select @str = @str + cast(dayz as varchar(300))+': ' FROM
(select top 1000 customer, orderdate,  convert(char(2),datepart(d,orderdate))+' '
+CONVERT(char(3),DATENAME(mm,orderdate))  as
dayz
from  CustomerOrder
where customer =@customer
order by orderdate
) as derived



if (len (@str) > 1)
  set @str = left(@str, len(@str) - 1)

return (@str)
End




----------------------------------------------------------------------------
------ run query and use function to return concatenated dates
----------------------------------------------------------------------------


select distinct customer, dbo.[GetCustomerOrderDates](123) as orderdates
from CustomerOrder