sql一列转多行

① sql中一对多关系的查询结果的多行转换成一行多列

--用动态sql实现行转列。因用到了row_number，只适用于sqlserver2005及以上版本
--测试数据
with
[user](ID,name,roleid)
as(
select1,'bobo','r1'unionall
select2,'coco','r1'unionall
select3,'dodo','r1'unionall
select4,'eoeo','r2'unionall
select5,'fofo','r2'),
[role](ID,name)
as(
select'r1','admin'unionall
select'r2','user')
--两表联合查询后暂存入临时表
selectb.IDroleid,b.namerolename,a.nameusername,row_number()over(partitionbyb.IDorderbya.ID)seq
into#t
from[user]a
innerjoin[role]bona.roleid=b.ID;
--拼接动态sql
declare@sqlvarchar(max);
set@sql='';
select@sql=@sql+
',max(caseseqwhen'+cast(tt.seqasvarchar)+'thenusernameelse''''end)user'+cast(tt.seqasvarchar)
from(selectdistinctseqfrom#t)tt
orderbyseq;
set@sql='selectrolename'+@sql+'from#tgroupbyroleid,rolename';
--打印动态sql
select@sql;
--执行动态sql
exec(@sql);
--删除临时表
droptable#t;

生成的动态sql为：

selectrolename,
max(caseseqwhen1thenusernameelse''end)user1,
max(caseseqwhen2thenusernameelse''end)user2,
max(caseseqwhen3thenusernameelse''end)user3
from#tgroupbyroleid,rolename

最终查询结果为：

② mssql2008将一列值转换多行，不要用while循环，

用union
select 557955 as a from temp where id=1
select 557956 as a from temp where id=2

③ SQL 单列转多列多行!

思路：先列出序号。用序号除4取余，余1就排在字段名称1下，余2就排在字段名称2下，楼主姐姐是这个意思吗?

select
case when ID%4=1 then 字段名称 end as 字段名称1
,case when ID%4=2 then 字段名称 end as 字段名称2
,case when ID%4=3 then 字段名称 end as 字段名称3
,case when ID%4=0 then 字段名称 end as 字段名称4
from
(select row_number() over(order by 字段名称) as ID, * from 表) as tb

④ MS SQL SERVER 如何把多列的值 , 变成一列多行 .

MS SQL SERVER没有这样的函数可以将多列转变为多行。
SELECT '小名' AS c1
union all
select '小名' AS c2
union all
select '小名' AS c3
union all
select '小名' AS c4
union all
select '小名' AS c5
union all
select '小名' AS c6
union all
select '小名' AS c8

⑤ SQL数据库 怎么将一行数据变成多行

查找：select
*
from
表名
where
cknum
='ck000010000002'
修改：update
表名
set
cknum
='-100.00'
where
cknum
='ck000010000002'
添加：insert
into
表名（字段1,字段2,字段3…)
values(值1,值2,值3…)
注意：先执行查找操作，添加语句中的值1，值2……按查询结果添加，然后就可以执行修改和添加操作了。
如果表里设置主键的话，新值会插入不进去。

⑥ sql server ，求sql 语句把单列的数据分为多行显示

-- 较长，凑合用吧，表名test, 三个字段分别设为yf,fy,je

select
yf,
max(case when fy in ('诊疗费','注射费') then fy else null end),
max(case when fy in ('诊疗费','注射费') then je else null end),
max(case when fy in ('治疗费','其他费') then fy else null end),
max(case when fy in ('治疗费','其他费') then je else null end)
from (select
t.*,
case
when fy in ('诊疗费','治疗费') then 1
when fy in ('注射费','其他费') then 2
end grp
from (select yf,fy,je from test where fy in ('诊疗费','治疗费','注射费')
union all
select yf,'其他费' fy,sum(je)je from test
where fy not in ('诊疗费','治疗费','注射费')
group by yf) t) t1
group by yf,grp

-- 下面这个语句仅供参考，为其他费用不需汇总时提供一个思路

select
yf,
max(case when id%2 <> 0 then fy else null end),
max(case when id%2 <> 0 then je else null end),
max(case when id%2 = 0 then fy else null end),
max(case when id%2 = 0 then je else null end)
from
(select
t1.*,
case when id % 2 = 0 then id - 1 else id end grp
from (select
t.*,row_number()over(order by yf) id
from test t) t1) t2
group by yf,grp