--測試環境mssql2008

--這就是行列轉換

Create TAble T

(

日期 date,

時間 time,

裝置 Varchar(10),

數值 int

)

insert into T values("2015-10-15","10:11:12",1,10)

insert into T values("2015-10-15","10:11:12",2,11)

insert into T values("2015-10-15","10:11:12",3,12)

insert into T values("2015-10-15","10:11:12",4,13)

insert into T values("2015-10-15","10:11:12",5,14)

insert into T values("2015-10-15","10:11:12",6,10)

insert into T values("2015-10-15","10:11:12",7,11)

insert into T values("2015-10-15","10:12:12",1,2)

insert into T values("2015-10-15","10:12:12",2,3)

insert into T values("2015-10-15","10:12:12",3,4)

select * from t

pivot

(

sum(數值)

for

裝置 in([1],[2],[3],[4],[5],[6],[7])

)p

select 日期,時間,

SUM(Case when 裝置=1 then 數值 else 0 end) As [1],

SUM(Case when 裝置=2 then 數值 else 0 end) As [2],

SUM(Case when 裝置=3 then 數值 else 0 end) As [3],

SUM(Case when 裝置=4 then 數值 else 0 end) As [4],

SUM(Case when 裝置=5 then 數值 else 0 end) As [5],

SUM(Case when 裝置=6 then 數值 else 0 end) As [6],

SUM(Case when 裝置=7 then 數值 else 0 end) As [7]

from t

Group by 日期,時間