固定行转列用csse when 就行了交叉表语句的实现:
--用于:交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name
--用于:交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name
As
Select
A.PKId,
A.name,
Max(Case type When 1 Then state Else 0 End) As type1,
Max(Case type When 2 Then state Else 0 End) As type2,
Max(Case type When 3 Then state Else 0 End) As type3,
Max(Case type When 4 Then state Else 0 End) As type4,
Max(Case type When 5 Then state Else 0 End) As type5,
Max(Case type When 6 Then state Else 0 End) As type6,
Max(Case type When 7 Then state Else 0 End) As type7,
Max(Case type When 8 Then state Else 0 End) As type8
From table1 A
Inner Join table2 B
On A.PKId=B.table1_PKId
Group By A.PKId,A.name
GO
select
a.PKId ,
a.name ,
type1=sum(case b.type when 1 then b.state esle 0 end),
type2=sum(case b.type when 2 then b.state esle 0 end),
type3=sum(case b.type when 3 then b.state esle 0 end),
type4=sum(case b.type when 4 then b.state esle 0 end),
type5=sum(case b.type when 5 then b.state esle 0 end),
type6=sum(case b.type when 6 then b.state esle 0 end),
type7=sum(case b.type when 7 then b.state esle 0 end),
type8=sum(case b.type when 8 then b.state esle 0 end)
from
table1 a,
table2 b
where
a.PKId=b.table1_PKId
group by
a.PKId,a.name
go
Create Table table1(PKId Int, name Varchar(100))
Create Table table2(table1_PKId Int,type Int,state Int)
Insert table1 Select 1, 'test'
Insert table2 Select 1,1,2
Union All Select 1,2,3
Union All Select 1,3,4
Union All Select 1,4,1
Union All Select 1,5,2
Union All Select 1,6,3
Union All Select 1,7,5
Union All Select 1,8,6
GO
--建立視圖
Create View V_List
As
Select
A.PKId,
A.name,
Max(Case type When 1 Then state Else 0 End) As type1,
Max(Case type When 2 Then state Else 0 End) As type2,
Max(Case type When 3 Then state Else 0 End) As type3,
Max(Case type When 4 Then state Else 0 End) As type4,
Max(Case type When 5 Then state Else 0 End) As type5,
Max(Case type When 6 Then state Else 0 End) As type6,
Max(Case type When 7 Then state Else 0 End) As type7,
Max(Case type When 8 Then state Else 0 End) As type8
From table1 A
Inner Join table2 B
On A.PKId=B.table1_PKId
Group By A.PKId,A.name
GO
--測試
Select * From V_List
GO
--刪除測試環境
Drop Table table1,table2
Drop View V_List
GO
--結果
/*
PKId name type1 type2 type3 type4 type5 type6 type7 type8
1 test 2 3 4 1 2 3 5 6
*/
如果相同的table1_PKId,一個type只有一個state。就用
Create View V_List
As
Select
A.PKId,
A.name,
Max(Case type When 1 Then state Else 0 End) As type1,
Max(Case type When 2 Then state Else 0 End) As type2,
Max(Case type When 3 Then state Else 0 End) As type3,
Max(Case type When 4 Then state Else 0 End) As type4,
Max(Case type When 5 Then state Else 0 End) As type5,
Max(Case type When 6 Then state Else 0 End) As type6,
Max(Case type When 7 Then state Else 0 End) As type7,
Max(Case type When 8 Then state Else 0 End) As type8
From table1 A
Inner Join table2 B
On A.PKId=B.table1_PKId
Group By A.PKId,A.name
GO否則,就用--建立視圖
Create View V_List
As
Select
A.PKId,
A.name,
SUM(Case type When 1 Then state Else 0 End) As type1,
SUM(Case type When 2 Then state Else 0 End) As type2,
SUM(Case type When 3 Then state Else 0 End) As type3,
SUM(Case type When 4 Then state Else 0 End) As type4,
SUM(Case type When 5 Then state Else 0 End) As type5,
SUM(Case type When 6 Then state Else 0 End) As type6,
SUM(Case type When 7 Then state Else 0 End) As type7,
SUM(Case type When 8 Then state Else 0 End) As type8
From table1 A
Inner Join table2 B
On A.PKId=B.table1_PKId
Group By A.PKId,A.name
GO