How to get comma separated values in query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get comma separated values in query?

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2009-02-05 : 00:24:21

hi
i have a set of tasks assigned to a set of employees.
there are chances where more than one employee has worked on a particular task. so, if two have worked on tht task, two entries will be made in the timesheet table against the same task.

Now i want to get only one row for tht task. so i need to display the names of employees as csv.

i came to know about the STUFF() in one of the older posts. can anyone explain how i can use it for my query?

this is my query:

select distinct
STUFF((SELECT e.emp_name+ ','
FROM employee e INNER JOIN project_log_time plt
ON e.emp_seq_no = plt.emp_seq_no WHERE plt.plt_object = 90120 FOR XML PATH('')),1,1,'')AS Value

but this is trimming the first letter of the name.
for ex if the name is John, i am getting "ohn".

Please advice.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 00:30:50

select distinct
STUFF((SELECT ',' + e.emp_name
FROM employee e INNER JOIN project_log_time plt
ON e.emp_seq_no = plt.emp_seq_no WHERE plt.plt_object = 90120 FOR XML PATH('')),1,1,'')AS Value

try like this

mrm23
Posting Yak Master

198 Posts

Posted - 2009-02-05 : 00:35:34

Thanks a lot. it works!!!!!!

But now i have one more problem. i am not getting distinct values i.e, say two persons A and B have worked on a task T.
A has worked for one day and B has worked for two days.(so there will be two entries for B.)
now i am getiing A,B,B. is it bcoz of the change in date?
I need to select these rows based on a date range. how to do it?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 00:43:41

welcome
u give the startdate and enddate
decalre @startdate datetime, @enddate datetime
select distinct
STUFF((SELECT ',' + e.emp_name
FROM employee e INNER JOIN project_log_time plt
ON e.emp_seq_no = plt.emp_seq_no WHERE plt.plt_object = 90120 and dateadd(dd,datediff(dd,0,datecolumn),0) between @startdate and @endate
FOR XML PATH('')),1,1,'')AS Value

mrm23
Posting Yak Master

198 Posts

Posted - 2009-02-05 : 05:35:36

hi
i am not able to use this in my sp.
i tried with coalesce. it works fine with a query but i am not able to use it in my procedure.

can someone help me ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 05:37:42

Do you get an error?
Do you get unexpected result?
Do you get a result at all?

E 12°55'05.63"
N 56°04'39.26"

kumarvashanth
Starting Member

4 Posts

Posted - 2009-05-04 : 01:40:26

quote:
Originally posted by bklr

welcome
u give the startdate and enddate
decalre @startdate datetime, @enddate datetime
select distinct
STUFF((SELECT ',' + e.emp_name
FROM employee e INNER JOIN project_log_time plt
ON e.emp_seq_no = plt.emp_seq_no WHERE plt.plt_object = 90120 and dateadd(dd,datediff(dd,0,datecolumn),0) between @startdate and @endate
FOR XML PATH('')),1,1,'')AS Value

Hi,

your post helps me.
For me also the same requirement with small modification
my requirement is i have to display for all employees
with
emp_seq_no emp_Name because i have to join this view
with another view by using emp_seq_no

can you help me in this

thanks
kumarvashanth

kumarvashanth
Starting Member

4 Posts

Posted - 2009-05-04 : 01:49:32

quote:
Originally posted by bklr

select distinct
STUFF((SELECT ',' + e.emp_name
FROM employee e INNER JOIN project_log_time plt
ON e.emp_seq_no = plt.emp_seq_no WHERE plt.plt_object = 90120 FOR XML PATH('')),1,1,'')AS Value

try like this

Hi

your query helped me
for me this is the current layout
PID VID COM
1 1 neckpain
1 1 backpain
1 1 headache
2 2 throatpain
3 3 neckpain

My required layout is like this

PID VID COM
1 1 neckpain,backpain,headache
2 2 throatpain
3 3 neckpain

here i need all three columns for all VID column

kindly help me in this

thanks
kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-04 : 06:46:42

[code]
-- DATA
declare @table table
(
PID int,
VID int,
COM varchar(10)
)
insert into @table
select 1, 1, 'neckpain' union all
select 1, 1, 'backpain' union all
select 1, 1, 'headache' union all
select 2, 2, 'throatpain' union all
select 3, 3, 'neckpain'

-- QUERY
select PID, VID,
COM = STUFF((SELECT TOP 100 PERCENT ',' + x.COM FROM @table x
WHERE x.PID = t.PID
AND x.VID = t.VID
ORDER BY ',' + x.COM FOR XML PATH('')), 1, 1, '')
from @table t
group by PID, VID

/* RESULT

PID VID COM
----------- ----------- --------------------------
1 1 backpain,headache,neckpain
2 2 throatpain
3 3 neckpain

(3 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources