• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

SQL行列互换

SQLServer eason 2323次浏览 2个评论 扫描二维码

列转行(一行转多行)

原表(Test20151104):

SQL行列互换

目标表:

SQL行列互换

sql-1:

   select a.* from
   (select YearMonth,Brand,'C' AS ACCNO,C AS Total from Test20151104
   union all
   select YearMonth,Brand,'I' AS ACCNO,I AS Total from Test20151104
   union all
   select YearMonth,Brand,'P' AS ACCNO,P AS Total from Test20151104
   union all
   select YearMonth,Brand,'W' AS ACCNO,W AS Total from Test20151104
   ) a order by Brand

sql-2:

   select YearMonth,Brand,ACCNO,Total
   from Test20151104
   unpivot
   (Total for ACCNO in(c,i,p,w))
   as x

行转列(一列转多列)

以之前对Test20151104的查询结果生成新表Test20151104_2

  select x.* into Test20151104_2 from
   (select YearMonth,Brand,ACCNO,Total
                from Test20151104
                unpivot
                (Total for ACCNO in(c,i,p,w))
                as unpvt
        )x

原表(Test20151104_2):

SQL行列互换

目标表:

SQL行列互换

Sql-1:

  select YearMonth,Brand,
   sum(case when ACCNO='C' then Total else 0 end) AS C,
   sum(case when ACCNO='I' then Total else 0 end) AS I,
   sum(case when ACCNO='P' then Total else 0 end) AS P,
   sum(case when ACCNO='W' then Total else 0 end) AS W
   from Test20151104_2
  group by YearMonth,Brand  

Sql-2:

   select YearMonth,Brand,C,I,P,W 
        from Test20151104_2
        pivot (sum(Total) for ACCNO in(C,I,P,W)) AS x 

金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明SQL行列互换
喜欢 (1)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. I cannot thank you enough for the blog article.Really looking forward to read more. Much obliged.
    dildo anal2016-01-01 23:15 Reply Windows XP | Opera 9.64
  2. 不错的网站
    harries2015-11-05 21:56 Reply Windows 7 | Chrome 48.0.2547.0