行轉(zhuǎn)列
一張表
查詢結(jié)果為
--行轉(zhuǎn)列
select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,
(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,
(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3
from Tb_Amount group by years
或者為
select years as 年份,
sum(case when month='1' then amount end) as 一月,
sum(case when month='2' then amount end) as 二月,
sum(case when month='3' then amount end) as 三月
from dbo.Tb_Amount group by years order by years desc
2.人員信息表包括姓名 時代 金額
顯示行轉(zhuǎn)列
姓名 時代 金額
姓名 年輕 中年 老年
張麗 1000000.00 4000000.00 500000000.00
孫子 2000000.00 12233335.00 4552220010.00
select uname as 姓名,
SUM(case when era='年輕' then amount end) as 年輕,
SUM(case when era='中年' then amount end) as 中年,
SUM(case when era='老年' then amount end) as 老年
from Tb_People group by uname order by uname desc
3.學(xué)生表 [Tb_Student]
顯示效果
靜態(tài)SQL,指subject只有語文、數(shù)學(xué)、英語這三門課程。
select sname as 姓名,
max(case Subject when '語文' then grade else 0 end) as 語文,
max(case Subject when '數(shù)學(xué)' then grade else 0 end) as 數(shù)學(xué),
max(case Subject when '英語' then grade else 0 end) as 英語
from dbo.Tb_Student group by sname order by sname desc
--動態(tài)SQL,指subject不止語文、數(shù)學(xué)、英語這三門課程。
declare @sql varchar(8000)
set @sql = 'select sname as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then grade else 0 end) [' + Subject + ']'
from (select distinct Subject from Tb_Student) as a
set @sql = @sql + ' from Tb_Student group by sname order by sname desc'
exec(@sql)
oracle中Decode()函數(shù)使用 然后將這些累計(jì)求和(sum部分）
select t.sname AS 姓名,
sum(decode(t.subject,'語文',grade,null))語文 ,
sum(decode(t.subject,'數(shù)學(xué)',grade,null)) 數(shù)學(xué),
sum(decode(t.subject,'英語',grade,null)) 英語
from Tb_Student t group by sname order by sname desc
列轉(zhuǎn)行
生成
sql代碼
生成靜態(tài):
select *
from (select sname,[Course ] ='數(shù)學(xué)',[Score]=[數(shù)學(xué)] from Tb_students union all
select sname,[Course]='英語',[Score]=[英語] from Tb_students union all
select sname,[Course]='語文',[Score]=[語文] from Tb_students)t
order by sname,case [Course] when '語文' then 1 when '數(shù)學(xué)' then 2 when '英語' then 3 end
go
--列轉(zhuǎn)行的靜態(tài)方案：UNPIVOT,sql2005及以后版本
SELECT sname,Subject, grade
from dbo.Tb_students
unpivot(grade for Subject in([語文],[數(shù)學(xué)],[英語]))as up
GO
--列轉(zhuǎn)行的動態(tài)方案：UNPIVOT,sql2005及以后版本
--因?yàn)樾惺莿討B(tài)所以這里就從INFORMATION_SCHEMA.COLUMNS視圖中獲取列來構(gòu)造行，同樣也使用了XML處理。
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Tb_students') and Name not in('sname')
order by Colid
exec('select sname,[Subject],[grade] from Tb_students unpivot ([grade] for [Subject] in('+@s+'))b')
go
select
sname,[Subject],[grade]
from
Tb_students
unpivot
([grade] for [Subject] in([數(shù)學(xué)],[英語],[語文]))b
您可能感興趣的文章:- Python SqlAlchemy動態(tài)添加數(shù)據(jù)表字段實(shí)例解析
- java使用JDBC動態(tài)創(chuàng)建數(shù)據(jù)表及SQL預(yù)處理的方法
- 如何將Oracle的一個大數(shù)據(jù)表快速遷移到 Sqlserver2008數(shù)據(jù)庫(圖文教程)
- Angualrjs和bootstrap相結(jié)合實(shí)現(xiàn)數(shù)據(jù)表格table
- bootstrap table 數(shù)據(jù)表格行內(nèi)修改的實(shí)現(xiàn)代碼
- MySQL中大數(shù)據(jù)表增加字段的實(shí)現(xiàn)思路
- mysql中數(shù)據(jù)庫與數(shù)據(jù)表編碼格式的查看、創(chuàng)建及修改
- Android實(shí)現(xiàn)仿excel數(shù)據(jù)表格效果
- MySQL清空數(shù)據(jù)表的方法實(shí)例與分析
- jQuery EasyUI框架中的Datagrid數(shù)據(jù)表格組件結(jié)構(gòu)詳解
- MySQL中復(fù)制數(shù)據(jù)表中的數(shù)據(jù)到新表中的操作教程
- 詳解數(shù)據(jù)庫中跨庫數(shù)據(jù)表的運(yùn)算
