有关sql语句的行列转换

在论坛看到sql行列转换，标记一下。http://www.iteye.com/topic/980212

 

userinfo 表数据



userdetail 表数据



SQL执行结果

 

 原始方法：

1. SELECT a.NAME AS 姓名,
2. NVL((SELECT b.VALUE FROM userdetail b WHERE USER_DATA='地址' AND a.ID=b.USER_ID),'空') AS 地址,
3. NVL((SELECT c.VALUE FROM userdetail c WHERE USER_DATA='电话' AND a.ID=c.USER_ID),'空') AS 电话,
4. NVL((SELECT d.VALUE FROM userdetail d WHERE USER_DATA='证件号' AND a.ID=d.USER_ID),'空') AS 证件号,
5. NVL((SELECT e.VALUE FROM userdetail e WHERE USER_DATA='QQ' AND a.ID=e.USER_ID),'空') AS QQ,
6. NVL((SELECT f.VALUE FROM userdetail f WHERE USER_DATA='邮编' AND a.ID=f.USER_ID),'空') AS 邮编
7. FROM
8. (SELECT * FROM userinfo ) a

使用case：

 

select b.name, a.*
  from (select user_id,
               max(case
                     when user_date = '地址' then
                      value
                   end) 地址,
               max(case
                     when user_date = '电话' then
                      value
                   end) 电话,
               max(case
                     when user_date = '证件号' then
                      value
                   end) 证件号
          from userdetail
         group by user_id) a, userinfo b
 where a.user_id = b.id