sql数据表行转列

二個數據表,一個員工資料表(Users),另外一個員工學歷表(UserInfo);執行sql語結果為:

   

sql Code

 

select

a.userno,

(select username from users where userno=a.userno)as username,

(select address from users where userno=a.userno)as address,

a.本科,

a.專科,

a.高中,

a.初中,

a.小學

from (

        select

            b.userno,

            max(case b.subject when '本科' then b.school1 else null end) as 本科,

            max(case b.subject when '專科' then b.school1 else null end) 專科,

            max(case b.subject when '高中' then b.school1 else null end) 高中,

            max(case b.subject when '初中' then b.school1 else null end) 初中,

            max(case b.subject when '小學' then b.school1 else null end) 小學

        from UserInfo b group by b.UserNo

    )a