CODEWITHSUNDEEP
sql-servert-sql
Tony
Tony

Reputation: 12715

TSQL How to build a string dynamically?

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 ?

Upvotes: 2

Views: 18437

Answers (2)

user372724
user372724

Reputation:

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

Upvotes: 0

gjvdkamp
gjvdkamp

Reputation: 10536

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

Upvotes: 3

Related Questions