求SQL查询语句怎么写

Question

格式
ID 值
A 1
A 2
B 3
B 4
B 5
C 6
C 7
……
显示
ID 列1 列2 列3 列4 列5 列6
A 1 2
B 3 4 5
C 6 7
……
补充一下，需求是 以ID字段行横排显示与ID相关值， 值字段的数值（字符）是随机的， 同一ID字段的值不会超过5个，

Answer 1

select id,group_concat(值) from tablename group by id
要是oracle那就是wm_concat()

只能做到这样了。追问

我用的SQLSERVER2008额，查了好久没 有这个函数哎

追答

我只说了mysql和oracle的。要是SQLSERVER，有以下几种方式

1 通过 select 累加

2 通过 FOR xml path('') 合并字符串记录

3 分组合并字符串记录

可参考：网页链接

Answer 2

select id,
case when shuzhi='1' then 1 when shuzhi like '1,%' then 1
when shuzhi like '%,1' then 1 when charindex(',1,',shuzhi)>0 then 1
else null end as shuzhi1,
case when shuzhi='2' then 2 when shuzhi like '2,%' then 2
when shuzhi like '%,2' then 2 when charindex(',2,',shuzhi)>0 then 2
else null end as shuzhi2,
case when shuzhi='3' then 3 when shuzhi like '3,%' then 3
when shuzhi like '%,3' then 1 when charindex(',3,',shuzhi)>0 then 3
else null end as shuzhi3,
case when shuzhi='4' then 4 when shuzhi like '4,%' then 4
when shuzhi like '%,4' then 4 when charindex(',4,',shuzhi)>0 then 4
else null end as shuzhi4,
case when shuzhi='5' then 5 when shuzhi like '5,%' then 5
when shuzhi like '%,5' then 5 when charindex(',5,',shuzhi)>0 then 5
else null end as shuzhi5,
case when shuzhi='6' then 6 when shuzhi like '6,%' then 6
when shuzhi like '%,6' then 6 when charindex(',6,',shuzhi)>0 then 6
else null end as shuzhi6
from tableName;追问

补充一下，需求是 以ID字段行横排显示与ID相关值字段， 值字段的数值（字符）是随机的， 同一ID字段的值不会超过5个，

本回答被网友采纳