2

let's say that I have a table with columns like this:

| Name | Cat_id |

I cannot remember the function which can build a varchar like this

1,24|4,56|5,67

where the number before the comma is the Cat_id, and the number after comma is the count of rows with that ID. Any ideas ?

Tony
  • 12,405
  • 36
  • 126
  • 226
  • 1
    Duplicate of: http://stackoverflow.com/questions/941103/concat-groups-in-sql-server – littlegreen Dec 12 '10 at 18:49
  • Are the pipe symbol part of the output? Is the output just 1 line with pipe delimited values of cat_id, count(cat_id) or you are expecting one row per cat_id? –  Dec 12 '10 at 18:49

2 Answers2

3

This could do the trick: 

declare @s varchar(8000)
set     @s = ''

select  @s = @s + cast(cat_id as varchar(20)) + ',' + cast(count(*) as varchar(20)) + '|'
from    SomeTable
group by cat_id
option(maxdop 1) -- this sure there are no funny side-effects because of parallelism

print @s

Alternatively you could use 'for xml', or a cursor, but this should be faster.

Regards GJ

gjvdkamp
  • 9,929
  • 3
  • 38
  • 46
  • hm, the count(*) returns always 1, even if I create a subquery (select count(*) from SomeTable where cat_id = st.cat_id) instead of that count(*) – Tony Dec 12 '10 at 20:01
  • Should work... sure you dont just hae unique id's? (not unlikely...) – gjvdkamp Dec 12 '10 at 20:09
  • Crap, sorry, read it wrong. Make it @S = @s + name + ',' .. etc .. group by name. We're grouping on Cat_id instead of name. EDIT: nope, you did ask for group by cat_id. You sure about that? – gjvdkamp Dec 12 '10 at 20:11
0

Hope this will help(sql server 2005+)... I have not tested the program as I donot have SQL Server at present

With Cte1 As(
Select Cat_ID, Count(Cat_ID),Cast(Cat_ID as Varchar) + Cast(Count(Cat_ID)) as MergedColumn from tbl 
group by Cat_ID),
cte2 as(
Select 
    Cat_ID
    , mergedData  = stuff((Select cast(MergedColumn + '|' as varchar) from tbl t2 where t2.Cat_ID  = t1.Cat_ID 
                  for xml path('')),1,1,'')
from tbl t1)

select mergedData from Cte2