Ⅰ 行转列，列转行怎么做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列转行
为何硬是要到数据库里变换呢？程序后台代码不是很好操作么？