--基本思路：用二进制对应字符串组合，例如aaa,ccc,ddddd对应0x01101，即十进制的13。这样的话所有的字符串组合可以对应到0x00001-0x11111，即1-31
--首先要有一张数字表num，保存从1开始的十进制数。具体构造方法这里不写了，总之表里的数据像这个样子：
--然后就可以用下面的语句查询了：
--生成字符串的所有组合
--测试数据
with tableA (id,keystring) as (
select 121898, 'aaa' union all
select 121898, 'bb' union all
select 121898, 'cccc' union all
select 121898, 'ddddd' union all
select 121898, 'e' union all
select 198567, 'ff' union all
select 198567, 'ttt' union all
select 198567, 'z'
),
t1(id,keystring,seq,num) as (
select id,keystring
,row_number() over (partition by id order by keystring)
,(select count(*) from tableA b where a.id=b.id)
from tableA a
)
--根据n的二进制，取出字符串并聚合
SELECT B.id as id,
STUFF(
COALESCE(
(SELECT ',' + CAST(keystring AS VARCHAR(max)) AS [text()]
FROM t1 AS O
WHERE id=B.id and B.n & Power(2,O.seq-1) != 0
ORDER BY O.seq
FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), ''),1,1,'') AS keystring,
(SELECT count(*)
FROM t1 AS O1
WHERE id=B.id and B.n & Power(2,O1.seq-1) != 0) AS num
FROM (
SELECT DISTINCT A.id, C.n
from num AS C
INNER JOIN t1 A on C.n) B
ORDER BY id,num,B.n;
--查询结果：
