Ⅰ 行轉列，列轉行怎麼做sql
/*行轉列*/
SELECT*FROM[StudentScores]/*數據源*/ASP
PIVOT
(SUM(Score/*行轉列後列的值*/)FOR
p.Subject/*需要行轉列的列*/IN([語文],[數學],[英語],[生物]/*列的值*/)
)AST
/*列轉行*/
SELECTP.ProgrectName,P.Supplier,P.SupplyNumFROM(SELECTProgrectName,OverseaSupply,NativeSupply,
SouthSupply,NorthSupplyFROMProgrectDetail
)T
UNPIVOT
(
SupplyNumFORSupplierIN
(OverseaSupply,NativeSupply,SouthSupply,NorthSupply)
)P
Ⅱ sql列轉行,如何把表轉化為這個結果一共一行 一列.如何實現.
通用的辦法是用動態sql解決：
declare@sqlvarchar(2000);
set@sql='select''';
select@sql=@sql+列名+','from表名;
select@sql=left(@sql,len(@sql)-1)+''''
exec(@sql);
此外，SQL Server2005以上版本可以用XML Path解決。
SELECT列名+','FROM表名FORXMLPATH('')這樣將直接得到'a,b,c,d,d,e,'，需要進一步處理去掉最後的逗號
Ⅲ sql 列轉行
通用的方法用union，不同的資料庫可能有對應的方法，比如sqlserver的unpovit
select'A'key,Avaluefromtabname
union
select'B'key,Bvaluefromtabname
union
select'C'key,Cvaluefromtabname
union
select'D'key,Dvaluefromtabname
union
select'E'key,Evaluefromtabname
Ⅳ sql語句列轉行
我整理的行轉列的問題：
--創建tb表
createtabletb(姓名varchar(10),課程varchar(10),分數int)
insertintotbvalues('張三','語文',74)
insertintotbvalues('張三','數學',83)
insertintotbvalues('張三','物理',93)
insertintotbvalues('李四','語文',74)
insertintotbvalues('李四','數學',84)
insertintotbvalues('李四','物理',94)
go
select*Fromtb
--SQLSERVER2000靜態行轉列
select姓名as姓名,
max(case課程when'語文'then分數elsenullend)語文,
max(case課程when'數學'then分數elsenullend)數學,
max(case課程when'物理'then分數elsenullend)物理
fromtb
groupby姓名
--SQLSERVER2000動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)
declare@sqlvarchar(8000)
set@sql='select姓名'
select@sql=@sql+',max(case課程when'''+課程+'''then分數else0end)['+課程+']'
from(selectdistinct課程fromtb)asa
set@sql=@sql+'fromtbgroupby姓名'
exec(@sql)
--SQLSERVER2005靜態SQL。
select*from(select*fromtb)apivot(max(分數)for課程in(語文,數學,物理))b
--SQLSERVER2005動態SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+'],[','')+課程fromtbgroupby課程
set@sql='['+@sql+']'
exec('select*from(select*fromtb)apivot(max(分數)for課程in('+@sql+'))b')
希望對你的學習有幫助。
Ⅳ SQL 列轉行 合計
/*
鑒於一天最多隻有5條記錄,所以我的做法是重新分組給每用戶的5條記錄分配id,然後用普通的行轉列方法處理.
創建存儲過程row2col,
把以下過程的"你的表名"改成實際表名(只有一個地方而已)即可,
如果該表有個自增的id列,就不用做(1)這步
*/
CREATE PROCEDURE row2col
@dt datetime
AS
--(1)生成自增id
select id=IDENTITY(INT),*
into #t
from 你的表名
where dates = @dt
--(2)分組增加序號gid
select *,gid=(select count(*) from #t b where a.id >= b.id and a.userid = b.userid and a.dates = b.dates)
into #t2
from #t a
order by userid,dates
--(3)接著就是普通的行轉列做法
select userid,dates,max(case when gid = 1 then hour else 0 end) as hour_1,max(case when gid = 2 then hour else 0 end) as hour_2,
max(case when gid = 3 then hour else 0 end) as hour_3,max(case when gid = 4 then hour else 0 end) as hour_4,max(case when gid = 5 then hour else 0 end) as hour_5
from #t2
group by userid,dates
--(4)刪除臨時表
drop table #t
drop table #t2
go
/*
以上為過程,調用方法:
exec row2col '2010-06-02'
*/
--
已修改為可以傳參數了
Ⅵ SQL語句如何列變行
select 欄位名按照你要求的排序就行了
select a,b,c from tableName 那麼輸出就是 abc對應的順序
Ⅶ SQL語句 列轉行
-- ========================= PIVOT 行列轉置 ===========================
-- 1、【行列轉置PIVOT】
declare @Score table(StuNo varchar(10), StuName varchar(50), CourseName varchar(50), Score int)
insert into @Score
select '1', 'Tom', 'Math', 80 union all
select '1', 'Tom', 'English', 82 union all
select '1', 'Tom', 'Geography', 84 union all
select '2', 'Jone', 'Math', 79 union all
select '2', 'Jone', 'English', 88 union all
select '2', 'Jone', 'Geography',86
select * from @Score
SELECT StuNo, StuName, Math, English, [Geography]
FROM @Score PIVOT (MAX(Score) FOR CourseName in (Math, English, [Geography]) ) AS ScoreList
ORDER BY StuNo
-- 2、【列行轉置UNPIVOT】
declare @ScoreList table(StuNo varchar(10), StuName varchar(50), Math int, English int, [Geography] int)
insert into @ScoreList
select '1', 'Tom', 80, 82, 84 union all
select '2', 'Jone', 79, 88, 86
select * from @ScoreList
SELECT StuNo, StuName, CourseName, Score
FROM @ScoreList UNPIVOT (Score FOR CourseName in (Math, English, [Geography]) ) AS ScorePvtTable
ORDER BY StuNo
Ⅷ sql 最簡單的列轉行
set nocount on ;
declare @T table(ID int)
insert @T select 1
insert @T select 2
insert @T select 3
insert @T select 4
insert @T select 5
insert @T select 6
insert @T select 7
insert @T select 8
insert @T select 9
insert @T select 0
;with c
as
(
select
row=(row_number()over(order by (select 1))-1)/5,* from @T)
select
ID=(select rtrim(ID) from c where row=a.row for xml path(''))
from C a
group by row
ID
----------------------
12345
67890
Ⅸ sql列轉行
為何硬是要到資料庫里變換呢？程序後台代碼不是很好操作么？