How do I transform a table into a comma separated values?? If I have an "employeeid"  as 1 that has taken different orders, I want to display in the result set like this

Employeeid           orderid

1                            1024,1025,1028,1029

I know only to display as column:

CREATE function dbo.fn_inonerow(@P_empid int)
returns @v_table table(employeeid int,orderid int)
As

Begin

Insert into @v_table(employeeid,orderid)

  Select               a.employeeid,a.orderid 
  From                orders a
  inner join           employees b
  on                       b.employeeid=a.employeeid
  Where                b.employeeid=@p_empid

Return
End

GO

 

Stylish, i do not know you are working on ORACLE or SQL Server. If you are working on SQL, there is a tool called DTS  in 2000 and SSIS in 2005. You are use these tools to generate the CSV file as  that u want to.  Please let me know further and i might be able to assist you.

 

Thanks

create function [dbo].fn_inonerow(@p_empid int)
returns nvarchar(4000)
as
begin
 declare @orderids nvarchar(4000)
 select @orderids = coalesce(@orderids + ', ', '') +  rtrim(orderid)
 from                orders a
 inner join           employees b
 on                       b.employeeid=a.employeeid
 where                b.employeeid=@p_empid

 return @orderids
end
go

--Then call the function like this:
select [dbo].fn_inonerow(1) as orderids

Yaar echoes bhayena ta. Its still displaying as column. Yesari display bhari rakhya cha

Employeeid                         orderid

1                                         10248

1                                          10234

1                                          10343

1                                          10345

 

I want to display in a single row

1                              10248,10234,10343,10345

 

But You gave me the idea of using string function.

Thanks buddy I will try

 

Fewata I am using SQL Server 2000

Did you drop your old function? Drop your function and run my code. It should work and produce the result you're looking for. Your output suggests that it's still running the old code. My function is scalar, and should in no way return multiple rows.

To drop your old function run this code: 

drop function [dbo].fn_inonerow

Echoes, still around? hmmm... nice to see u again. Where r u these days?

stylish, PM me with your number.

 

Thanks

 

Bravo, superb echoes thank you... U r gr8

Credit goes to Echoes. I modified to code so that now its returning value to the table variable and displaying two columns employee id and the related orderid  separated by commas.

Thanks once again echoes.

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Alter function [dbo].fn_inonerow(@p_empid int)
Returns @v_table table(employeeid int,orderid nvarchar(4000))
As

Begin

Declare @orderids nvarchar(4000)
Select  @orderids=coalesce(@orderids + ',','') +  rtrim(orderid)
From                orders a
inner join          employees b
on                  b.employeeid=a.employeeid
where               b.employeeid=@p_empid

Insert into @v_table(employeeid,orderid)
Select Distinct    a.employeeid, @orderids
From                orders a
inner join          employees b
on                  b.employeeid=a.employeeid
Where               b.employeeid=@p_empid

Return
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--Select * from [dbo].fn_inonerow(3)

 

stylish - no problem. glad it worked.

>>Echoes, still around? hmmm... nice to see u again. Where r u these days?

Rusty, yes, evidently ;-). Good to hear from you. Same place. Have you moved?

 

OMG echoes uncle you're ALIVE??? 

Yes, niece. Hope it was not unexpected? ;-) How's school?

Who else is still here from the old days?

OMG nailu le echoes lai uncle re?

Echoes guruji how's everything? No time for trivia these days?

daZ DAI how's ashfield?? ;)

nailu a'field is great. Neps everywhere now.

really?? i am coming there REAL REAL soon.....;)