如何在SQL(HANA)中获得转置? [英] How to get the transpose in sql (HANA)?

问题描述

我有一个查询，返回的表是类似的

I have a query which returns the table which is like

 SELECT (CASE WHEN type=1 THEN 'A' 
                 WHEN type=2 THEN 'B' 

           END) as TYPE,COUNT(*) AS COUNT
            from TYPE_TABLE GROUP BY TYPE

(我用例说明每种类型的数量).

(I use case to have the count in each types).

count     type
_____     _____
123         A
124         B
120         C

我想让桌子像这样

 A       B       C
___     ___     ___
123      124     120

所以实际上我需要转置列数.如何在HANA中获取它?

So actually I need the transpose of the column count.How do I get this in HANA?

推荐答案

纯SQL解决方案( SQL Server )

select 
  [A] 
  , [B]
  , [C]
from
(
  select Type,[Count]
  from ( SELECT (CASE WHEN type=1 THEN 'A' 
             WHEN type=2 THEN 'B' 
             .....   
       END) as TYPE,COUNT(*) AS COUNT
        from TYPE_TABLE GROUP BY TYPE) Table1
) x
pivot
(
  SUM(Count)
  for Type in([A], [B], [C])
)p

演示

这篇关于如何在SQL(HANA)中获得转置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！