I need to combine multiple rows from a subquery into a string separated by a space

Question

I'm creating an image gallery with albums. The gallery owner can first create one or more albums, and then create images to add to those albums. I'm having to use multiple view templates to pull this off. Regardless, here's what is hanging me up. Here is the core of my stored procedure:

DECLARE @MyString VARCHAR(MAX)

SELECT @MyString = ISNULL(@MyString + ' ', ' ') + CAST(AlbumID AS VARCHAR(10)) + ' '
FROM BD_AlbumGallery
WHERE ImageID = PROBLEM HERE... Dont know what to do <---
ORDER BY AlbumID

SELECT a.[ImageID]
      ,a.ImageFile
      ,a.[ImageTitle]
      ,a.[ImageCaption]
      ,a.[Description]
      ,a.[Active]
      ,b.[AlbumID]
      ,b.[ImageID]
      ,c.[ListingID]
      ,c.[Active]
      ,c.[LevelID]
      ,c.[Title]

      ,@MyString AS AlbumClass

FROM BD_Gallery A
JOIN BD_AlbumGallery B
ON a.ImageID = b.ImageID
JOIN BD_Listing C
ON a.ListingID = c.ListingID
WHERE a.ListingID = @passedListingID
  AND a.Active = 1
  AND c.Active = 1
  AND c.LevelID > 5

So the problem is, the data is retrieved within a LI and one at a time of course... guess that's the obvious... to display properly in my gallery template.

The only issue I'm having is I need the @MyString AS AlbumClass to be tied to a.ImageID, or b.ImageID, as they are the same.

The reason is, the albums in the template are defined by a class.

<li class="12 24 15 17">Rest of my code for a single image result</li>

So the AlbumID's being utilized as class names allows me to assign them to the jQuery dropdown category/album list that's in a template right before this one. I was thinking of a SubQuery. Ultimately, I need the results (for other reasons) to display in one result set.

Please help.. Still trying to push my way through the learning curves.

Thanks!

Patrick

Maybe you're not too bothered about this, but class names shouldn't start with a number. http://stackoverflow.com/questions/448981/what-characters-are-valid-in-css-class-names Depending on what you want to do with it, you could add the ids in the `data` attribute instead. — Jan, Oct 22 '12 at 07:09
Thank you for pointing that out. I'll probably pull another field from the main listing table that doesn't have spaces. Then I have to figure out how to append it to the beginning of those AlbumIDs, and still keep it in the same row. :( — user1447679, Oct 22 '12 at 07:28
So perhaps I can use the AlbumTitle and place it before the ID. I'm unsure of how to remove spaces and other illegal characters in case the user has them in the Album name. Regardless, my end goal is I must have a unique identifier to the album, no spaces, legally used as a class name, and each image in it's LI the class names separated by a space... Like (albumtitleID albumtitle2ID albumtitle3ID) and all in one row. — user1447679, Oct 22 '12 at 07:32
[link](http://www.themepunch.com/codecanyon/megafolio/megafolio_light.html) If you view the source code you can see how it's structured and how the class names in the LI are used to assign a single image to more than one album during the view. — user1447679, Oct 22 '12 at 07:34
You can use the data attribute for that. http://stackoverflow.com/questions/1009485/jquery-filter-element-based-on-data-key-value — Jan, Oct 22 '12 at 07:39

Roman Pekar · Accepted Answer · 2012-10-22T08:06:29.663

2

select
    a.[ImageID], 
    a.ImageFile,
    a.[ImageTitle],
    a.[ImageCaption],
    a.[Description],
    a.[Active],
    c.[ListingID],
    c.[Active],
    c.[LevelID],
    c.[Title],
    stuff
    (
        (
        select ' ' + CAST(TT.AlbumID AS VARCHAR(10))
        from BD_AlbumGallery as TT
        where TT.[ImageID] = A.[ImageID]
        for xml path(''), type
        ).value('data(.)', 'nvarchar(max)')
    , 1, 1, '') as Listings
from BD_Gallery as A
    left outer join BD_Listing as C on a.ListingID = c.ListingID
where
    a.ListingID = @passedListingID and
    a.Active = 1 and c.Active = 1 and
    c.LevelID > 5

edited Oct 22 '12 at 08:06

answered Oct 22 '12 at 06:43

Roman Pekar

107,110
28
195
197

Roman, thank you for your fast response. I'm having a few small issues with this, but you seem to be getting me on the right track. I stripped down everything and here is what's happening. lets say I have 4 albums with the following IDs (1, 2, 3, 4) and those IDs are also going to be used as the classes in my LI. If I add an image to album 1, the SQL results for "Listings" is blank. If I add it to Album 2, so now in 1 and 2... the Listing results is (2) instead of (1 2) which is what I need. Weird. If I add it to album 3, the results are (2 3) which is weird. Also, I'm getting duplicate result – user1447679 Oct 22 '12 at 07:07
Ran out of room. Getting duplicate results. I need an image retrieved only one time, but the "Listings" column as you noted above needs to list the IDs of the album that the image is in. So if the image is in album 1, 2, 3 and 4, it needs to say (1 2 3 4) without () obviously. This is getting me close. Please offer more advice. Thank you so very much. – user1447679 Oct 22 '12 at 07:10
Roman this is very close. I've broken it down to test with one picture. I've placed the picture in Albums (1 2 3 4) The result is a single row for the picture, which is good, but it isn't picking up album 1. It's successfully picking up 2 3 4. – user1447679 Oct 22 '12 at 07:41
What would be your advice on adding one of the other field values to the front of the numbers to protect class name rules? Like "AlbumName_ID" or something like that. – user1447679 Oct 22 '12 at 07:43
Or perhaps I don't need another field. Is there a way to force a simple "ABC_AlbumID" in the results? By the way... I'm shocked at the community help on Stackoverflow. It's much appreciated... Thank you for taking the time to help. – user1447679 Oct 22 '12 at 07:45
It's working now.. But I'm not sure if it's solid? I deleted the album with the ID of "1" and created a new one in its place. I also changed , 1, 3, '' to , 1, 1, '' Is there something to do with the value 1 as a string? I'm a little confused. It's picking up as it should now, but I'm worried that something isn't right. Some rule of the number 1? – user1447679 Oct 22 '12 at 07:59
damn, I've wrote incorrect parameters of `stuff` function. You see, `stuff(@string, @x, @y, @r)` replacing `@y` characters from position `@x` in string @string with replacement `@r`. So `basicall stuff(..., 1, 3, '')` was written for string like ' ,1 , 2, 3'. I've changed an answer – Roman Pekar Oct 22 '12 at 08:06
Hah!!! Awesome!!! It's working!!! Do you know of a way I can place a letter or ... well, anything before each number for css purposes? You've done way more than anticipated... so feel free to ignore me. Appreciate the help. – user1447679 Oct 22 '12 at 08:13
well you see ' ' + placed before CAST(TT.AlbumID AS VARCHAR(10)). So you can basically place any number of characters (or another columns) there. But you need to correct parameters of stuff function - stuff intended to cut this symbols from beginning of your string. so it could be `'aaa: ' + CAST(TT.AlbumID AS VARCHAR(10))` and you should do `stuff(..., 1, 5, '')` – Roman Pekar Oct 22 '12 at 08:18
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/18392/discussion-between-user1447679-and-roman-pekar) – user1447679 Oct 22 '12 at 08:28

I need to combine multiple rows from a subquery into a string separated by a space

1 Answers1