怎么把SQL Unpivot查询转换成HANA SQL? [英] How to convert SQL Unpivot query to HANA SQL?

问题描述

如何将以下使用 unpivot 的SQL Server查询转换为HANA SQL查询.

How to convert following SQL Server query which uses unpivot to a HANA SQL query.

SELECT 
    p.CardName, 
    REPLACE(p.groupcode1, 'QryGroup', '') groupcode1, 
    ocqg.GroupName
FROM
    ocrd 
UNPIVOT 
    (value
     FOR groupcode1 IN ([QryGroup1], [QryGroup2], [QryGroup3], [QryGroup4], 
                        [QryGroup5], [QryGroup6], [QryGroup7], [QryGroup8],
                        [QryGroup9], [QryGroup10], [QryGroup11], [QryGroup12],
                        [QryGroup13], [QryGroup14], [QryGroup15],[QryGroup16],
                        [QryGroup17], [QryGroup18], [QryGroup19],[QryGroup20],
                        [QryGroup21], [QryGroup22], [QryGroup23],[QryGroup24],
                        [QryGroup25], [QryGroup26], [QryGroup27],[QryGroup28],
                        [QryGroup29], [QryGroup30], [QryGroup31],[QryGroup32],
                        [QryGroup33], [QryGroup34], [QryGroup35],[QryGroup36],
                        [QryGroup37], [QryGroup38], [QryGroup39],[QryGroup40],
                        [QryGroup41], [QryGroup42], [QryGroup43],[QryGroup44],
                        [QryGroup45], [QryGroup46], [QryGroup47],[QryGroup48],
                        [QryGroup49], [QryGroup50], [QryGroup51],[QryGroup52],
                        [QryGroup53], [QryGroup54], [QryGroup55],[QryGroup56],
                        [QryGroup57], [QryGroup58], [QryGroup59],[QryGroup60],
                        [QryGroup61], [QryGroup62], [QryGroup63],[QryGroup64])

     ) as p,
     ocqg
WHERE 
    value = 'Y' 
    AND ocqg.GroupCode = REPLACE(p.groupcode1, 'QryGroup', '')
ORDER BY 
    p.CardCode

推荐答案

我不认为Hana SQL支持横向联接(SQL Server中的 apply 关键字).一种方法就是 union all :

I don't think Hana SQL supports lateral joins (the apply keyword in SQL Server). One method is just union all:

select og.*
from (select o.cardname, g.groupcode,
             (case when g.groupcode = 1 then groupcode1
                   when g.groupcode = 2 then groupcode2
                   . . .
                   when g.groupcode = 64 then groupcode64
              end) as groupname
      from ocrd o cross join
           (select 1 as groupcode from dummy union all
            select 2 as groupcode from dummy union all
            . . .
            select 64 as groupcode from dummy
           ) g   
     ) og         
where og.groupname is not null;

这篇关于怎么把SQL Unpivot查询转换成HANA SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！