A. sql 行列轉換
行列轉換等經典SQL語句
1.--行列轉換
原表: 姓名 科目 成績
張三 語文 80
張三 數學 90
張三 物理 85
李四 語文 85
李四 物理 82
李四 英語 90
李四 政治 70
王五 英語 90
轉換後的表： 姓名 數學 物理 英語 語文 政治
李四 0 82 90 85 70
王五 0 0 90 0 0
張三 90 85 0 80 0
實例:
create table cj --創建表cj
(
ID Int IDENTITY (1,1) not null, --創建列ID,並且每次新增一條記錄就會加1
Name Varchar(50),
Subject Varchar(50),
Result Int,
primary key (ID) --定義ID為表cj的主鍵
);
--Truncate table cj
--Select * from cj
Insert into cj
Select '張三','語文',80 union all
Select '張三','數學',90 union all
Select '張三','物理',85 union all
Select '李四','語文',85 union all
Select '李四','物理',82 union all
Select '李四','英語',90 union all
Select '李四','政治',70 union all
Select '王五','英語',90
--行列轉換
Declare @sql varchar(8000)
Set @sql = 'Select Name as 姓名'
Select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result else 0 end) ['+Subject+']'
from (select distinct Subject from cj) as cj --把所有唯一的科目的名稱都列舉出來
Select @sql = @sql+' from cj group by name'
Exec (@sql)
2. 行列轉換--合並
原表: 班級 學號
1 1
1 2
1 3
2 1
2 2
3 1
轉換後的表: 班級 學號
1 1,2,3
2 1,2
3 1
實例:
Create table ClassNo --創建表ClassNo
(
ID Int IDENTITY(1,1) not null, --創建列ID,並且每次新增一條記錄就會加1
Class Varchar(50), --班級列
Number Varchar(50), --學號列
Primary Key(ID) --定義ID為表ClassNo的主鍵
);
--Truncate Table ClassNo
--Select * from ClassNo
Insert Into ClassNo
Select 1,1 Union all
Select 1,2 Union all
Select 1,3 Union all
Select 2,1 Union all
Select 2,2 Union all
Select 3,1
創建一個合並的函數
--Drop Function KFReturn
Create Function KFReturn(@Class Varchar(50))
Returns Varchar(8000)
as
Begin
Declare @str Varchar(8000)
Set @str = ''
Select @str = @str + cast(Number as Varchar(50)) + ',' from ClassNo Where Class = @Class
Set @str = SubString(@str,1,len(@str)-1)
Return(@str)
End
--調用自定義函數得到結果
Select Distinct Class,dbo.KFReturn(Class) From ClassNo
3:列轉行
--Drop Table ColumnToRow
Create table ColumnToRow
(
ID Int IDENTITY(1,1) not null, --創建列ID,並且每次新增一條記錄就會加1
a int,
b int,
c int,
d int,
e int,
f int,
g int,
h int,
Primary Key(ID) --定義ID為表ColumnToRow的主鍵
);
--Truncate Table ColumnToRow
--Select * from ColumnToRow
Insert Into ColumnToRow
Select 15,9,1,0,1,2,4,2 Union all
Select 22,34,44,5,6,7,8,7 Union all
Select 33,44,55,66,77,88,99,12
Declare @sql Varchar(8000)
Set @sql = ''
Select @sql = @sql + rtrim(name) + ' from ColumnToRow union all Select ' from SysColumns Where id = object_id('ColumnToRow')
Set @sql = SubString(@sql,1,len(@sql)-70)
--70的長度就是這個字元串'from ColumnToRow union all Select ID from ColumnToRow union all Select ',因為它會把ID這一列的值也算進去，所以要把它截掉
Exec ('Select ' + @sql + ' from ColumnToRow')
B. Sql 行列轉換
select * from
(
select CarNo as CarNo, Subject = '停車費' , Result = 停車費 from tb1
union all
select CarNo as CarNo, Subject = '路橋費' , Result = 路橋費 from tb1
union all
select CarNo as CarNo, Subject = '燃油費' , Result = 燃油費 from tb1
) t
order by name , case Subject when '停車費' then 1 when '路橋費' then 2 when '燃油費' then 3 when '金額' then 4 end
C. 如何用SQL語句達到行列轉置的效果求答案
有專門的行專列內置函數，oracle的叫pivot，你可以研究下。可以實現你要的效果
D. 寫sql，怎麼將查詢結果的行列轉換呀
有意思的問題 給出一個參考的URL： 假設你表是這樣的結構部件 入庫日期 入庫數量 A 1/1 10 A 1/1 5 A 1/2 10 B 1/5 10 其實就是兩種方法，假設你的資料庫是11以前的，只能先定義好查哪天到哪天 然後那麼 selct 部件， sum（ decode(入庫日期=1號，入庫數量，0）， sum（ decode(入庫日期=2號，入庫數量，0）， 以此類推 from 入庫表 group BY 部件（原理上就是將不是這天的變成0，再合計）要是Oracle資料庫是11的話，就簡單了，直接pivot搞定 pivot語法就不說了，網上一堆一堆的 ~
E. sql動態實現行列轉換
題：假設有張學生成績表(tb)如下:
姓名 課程 分數
張三 語文 74
張三 數學 83
張三 物理 93
李四 語文 74
李四 數學 84
李四 物理 94
想變成(得到如下結果)：
姓名 語文 數學 物理
---- ---- ---- ----
李四 74 84 94
張三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 靜態SQL,指課程只有語文、數學、物理這三門課程。(以下同)
select 姓名 as 姓名 ,
max(case 課程 when '語文' then 分數 else 0 end) 語文,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 課程 when ''' + 課程 + ''' then 分數 else 0 end) [' + 課程 + ']'
from (select distinct 課程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
F. sql 行列轉置
看看這個吧
SQLServer中(行列轉換)行轉列及列轉行且加平均值及匯總值
G. sql行列轉置語句
declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',max(case B when '+cast(B as varchar(10))+' then c end) ['+cast(B as varchar(10))+']'
from (Select distinct B from TB) x
set @sql='select A'+@sql+' from (select A,B,count(distinct C) c from tb
group by A,B) v
group by a'
exec(@sql)
H. SQL SERVER行列轉置
select '語文' 課程,avg(chinese) 平均分 from 表
union
select '數學' 課程,avg(math) 平均分 from 表
union
select '英語' 課程,avg(english) 平均分 from 表
I. SQL行列轉換
1> create table tb(姓名 varchar(10) , 課程 varchar(10) , 分數 int)
2> Insert tb
3> Select '張三','語文',60 union all
4> Select '張三','數學',70 union all
5> Select '張三','英語',80 union all
6> Select '張三','物理',90 union all
7> Select '李四','語文',65 union all
8> Select '李四','數學',75 union all
9> Select '李四','英語',85 union all
10> Select '李四','物理',95
11> go
(8 行受影響)
1>
2> SELECT
3> *
4> FROM
5> tb
6> PIVOT(
7> SUM([分數])
8> FOR [課程] IN ([語文],[數學] )
9> ) tmp
10> go
姓名 語文 數學
---------- ----------- -----------
李四 65 75
張三 60 70
(2 行受影響)
好像沒有辦法，課程名字必須寫在 SQL 裡面。
FOR [課程] IN ([語文],[數學] )
J. 在excel中用sql語句實現行列轉置
在excel中，使用SQL太麻煩了。
有專門的功能，叫透視表，可以試一下。