sql server - SQL Rollup Group By Single Column -

i have dataset consisting of applicationname (applicationalias) , distinct count of username

the user wants report rolling (the top 20 of these apps addition of username count (combined distinct) of previous rows.

• standard build apps - 6174
• standard build apps, adobe acrobat - 6397
• standard build apps, adobe acrobat, microsoft office word - 6605
• standard build apps, adobe acrobat, microsoft office word, microsoft office outlook - 6723
• standard build apps, adobe acrobat, microsoft office word, microsoft office outlook, microsoft office excel - 6859 -... (and on)

at moment can think of using set operator 20 union queries concatenation appears above, hardly ideal.

is there way?

i using sql server , single table of applicationalias , username

but there spanner in works in only want count users have applications in question, ie others outside list (ie standard build apps, adobe acrobat, microsoft office word) should not included.

i'm thinking near impossible in sql or @ least can done part statements gather data excel?

create table [dbo].[usersapps](     [username] [varchar](100) null,     [applicationalias] [nvarchar](100) null ) 

---

| username | applicationalias |

---

|10008 |standard build apps |

---

|10008 |adobe acrobat |

---

|10056 |microsoft office word |

---

|10056 |microsoft office excel|

---

|10059 |symantec pgp desktop |

---

so, given recent edit, looks want 2 columns, comma delimited list of apps , user.

"standard build apps, adobe acrobat", 10008

"microsoft office word, microsoft office excel", 10056

symantec pgp desktop", 10059

if want create comma delimited list in sql, can for xml path , stuff.

select apps =      stuff((select ', ' + applicationalias            usersapps b              b.username = a.username            xml path('')), 1, 2, ''),     username usersapps group username 

see sql fiddle

if want exclude application names, add not in clause.