Concatenating a List of of sites and count of occurrences into a column

Question

Using this page: Can I Comma Delimit Multiple Rows Into One Column?

... I've been able to come up with results such as this:

enter image description here

Using this query:

SELECT
    [Client ID],
    STUFF((SELECT ', ' + [Location (counts)]
           FROM (
                SELECT TOP 100 PERCENT [Client ID]
                    , ltrim(str([Store Num])) + ' (' + ltrim(str(count([Store Num]))) + ') ' [Location (counts)]
                FROM @tbl_coreData
                GROUP BY [Store Num], [Client ID]
                ORDER BY COUNT([Store Num]) DESC) tblThis 
           WHERE ([Store Num] = Result.[Store Num] 
             AND [Client ID] = Result.[Client ID]) 
           FOR XML PATH ('')),1,1,'') AS BATCHNOLIST
FROM @tbl_coreData AS Result
GROUP BY [Client ID], [Store Num]

I want the list to be ordered by The count of occurrences (the number in parenthesis). My attempt at this was the

SELECT TOP 100 PERCENT 
    [Client ID]

and

ORDER BY COUNT([Store Num]) DESC

but this didn't work.

How can I make the concatenated values be presented in order of descending occurrence?

As always, all help is welcome and appreciated.

score 2 · Answer 1 · answered Nov 10 '14 at 21:27

2

Something like this:

with pre AS
(
  SELECT 
    [Client ID],
    ltrim(str([Store Num])) as [Store Num]
    count([Store Num]) as locationcount
  FROM @tbl_coreData
  Group By [Store Num], [Client ID]
)
Select [Client ID],
       STUFF((SELECT ', ' + [Location (counts)]
              FROM (
                SELECT [Client ID], [Store Num] + ' (' + locationcount + ') ' [Location (counts)]
                FROM pre
                Order By locationcount desc
              ) tblThis 
              WHERE ([Store Num] = Result.[Store Num] AND [Client ID] = Result.[Client ID]) 
              FOR XML PATH ('')),1,1,'') AS BATCHNOLIST
From pre AS Result
order by locationcount desc

answered Nov 10 '14 at 21:27

Hogan

69,564
10
76
117

This is helpful in that I have never used a "WITH" clause. I was able to order values by adding *FROM pre Group By locationcount, [Store Num], [Client ID]* into the subquery, however this orders ascending by default. the *Order By* statements don't work in the subquery, although they don't throw an error either. – n8. Nov 10 '14 at 22:03
@n8 There should be no reason to order by in the sub-query (pre). Any time you use it you will be doing a select and can add an order by clause in that select. I'm not sure where you are not seeing the correct ordering. – Hogan Nov 11 '14 at 14:31
I don't understand why my edit of your answer was rejected, I made two minor revisions so that it would run. You have an *order by* in your subquery, maybe you mean to exclude that instead of making it work? As for my comment above, the *order by* that I mentioned using did not ultimately work, it seems that my preliminary results were a fluke of my test subject set. So the question remains open. – n8. Nov 12 '14 at 18:39
@n8 the orderby in the sub-query should change what order the comma separated results are in. the order by at the end should change the order of the rows. – Hogan Nov 12 '14 at 19:21

n8. · Accepted Answer · 2014-11-17T19:02:10.877

After much fidgeting and using Hogan's suggestion, I have come up with this:

DECLARE @Heroes TABLE (
    [HeroName]      VARCHAR(20),
    [HeroFriend]    VARCHAR(20)
)

INSERT INTO @Heroes ( [HeroName], [HeroFriend] )
VALUES  ( 'Superman', 'Aquaman' ), 
        ( 'Superman', 'Batman' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Superman', 'Batman' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Superman', 'Batman' ), 
        ( 'Superman', 'Ironman' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Superman', 'Batman' ), 
        ( 'Superman', 'Ironman' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Superman', 'Batman' ), 
        ( 'Superman', 'Ironman' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Superman', 'Wolverine' ), 
        ( 'Batman', 'Wolverine' );

with pre as (
  SELECT
    [HeroName],
    count([HeroName]) [locationcount],
    '(' + ltrim(str(count([HeroName]))) + ') ' + [HeroFriend] [Concat]
  FROM @Heroes
  Group By [HeroName],
           [HeroFriend]
)

Select DISTINCT
       [HeroName],
       STUFF((SELECT ',' + [Concat]
              FROM (
                    SELECT TOP 100 [HeroName],[Concat]
                    FROM pre
                    order by locationcount desc
              ) tblThis 
              WHERE ([HeroName] = Result.[HeroName]
                 )
              FOR XML PATH ('')),1,1,'') AS [Loc Counts]
From pre AS Result

For some reason SQL didn't like it much when I tried to do the Concatenation and/or sorting by counts on the fly. The code would run without error, but the sorting was disregarded. But putting those into the "with" block beforehand solved it.

Concatenating a List of of sites and count of occurrences into a column

2 Answers2