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