result with comma delimited field

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 2008 Forums

Transact-SQL (2008)

result with comma delimited field

Author

Topic

redhawk
Starting Member

8 Posts

Posted - 2011-05-17 : 10:12:25

I'm trying to create query returning comma delimited results for one field (photo). code:

select distinct a.Id ,a.status as AccountStatusId, 
	LTRIM((select PhotoName+',' from UploadPhotoReport where id=u.Id))as photo
	from Accounts a with (nolock)
	inner join UploadPhotoReport u on a.Id=u.AccountId
	where u.PhotoName<>'' and a.status in(12,200)
	order by a.Id desc

What i'm getting now is multiple rows and I want one row of this:
Id StatusId photo
541884988 200 ImgA17520111252541884988.JPG,
541884988 200 ImgC11252011949541884988.JPG,
541884988 200 ImgC21252011949541884988.JPG,
541884988 200 ImgC317520111252541884988.JPG,
541884988 200 ImgS17520111111541884988.JPG,

Red

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-17 : 10:20:08

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

redhawk
Starting Member

8 Posts

Posted - 2011-05-17 : 10:41:36

Thanks khtan, but I got : Subquery returned more than 1 value. This is not permitted when the subquery follows...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-17 : 10:57:29

You need to follow the link posted

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 12:12:07

It may have to do with how you concatenated, or interpreted the script in the link KH posted. Part of your query where you have the inner select should look something like this :

   LTRIM(
       (
          SELECT 
             STUFF(
            (SELECT
               ',' AS [text()],
                 PhotoName  AS [text()]
            FROM
                 UploadPhotoReport
            WHERE
                 id = u.Id
            FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
       )
   ) AS photo

redhawk
Starting Member

8 Posts

Posted - 2011-05-18 : 03:08:55

I've thought this one will be easier

quote:
Originally posted by madhivanan

You need to follow the link posted

Madhivanan

Failing to plan is Planning to fail

redhawk
Starting Member

8 Posts

Posted - 2011-05-18 : 03:54:17

Cheers mates I've done it :)


STUFF((select photoname +',[' + cast(DateAdded as varchar(100))+']' from UploadPhotoReport where AccountId=a.Id order by dateadded desc FOR XML PATH('')), 1, 0, '') AS Photos
from ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources