SYS_CONNECT_BY_PATH在Server动态使用中的应用

Question

我目前正在一个从Oracle Exadata (R.I.P :( )到Server 2008 )的迁移项目中。

我在Oracle中有几个查询，其中包含了很好的SYS_CONNECT_BY_PATH函数。

对于我的问题:我有当前的数据集

ORDER_ID   ORDER_GROUP_ID   OPERATOR_ID   GROUP_NAME    VALUE_ID    DESCRIPTION
--------------------------------------------------------------------------------------------------------------
1             10000            3          USER_ID       not null    'panel_id or msisdn_anonym of user'
2             10000            3          MISSING_FLAG     0        'data for extrapolation are not missing' 
3             10000            3          MISSING_FLAG     1        'data for extrapolation are missing'
5             10000            3          PANEL_FLAG       0        'source of user: no panel'
5             10000            3          PANEL_FLAG       1        'source of user: panel'
6             10000            3          ACTIVE_FLAG      0        'not active user'
7             10000            3          ACTIVE_FLAG      1        'active user'
1             10000            5          USER_ID      not null     'panel_id or msisdn_anonym of user'
2             10000            5          MISSING_FLAG     0        'data for extrapolation are not missing'
3             10000            5          MISSING_FLAG     1        'data for extrapolation are missing'  
5             10000            5          PANEL_FLAG       0        'source of user: no panel'
5             10000            5          PANEL_FLAG       1        'source of user: panel'
6             10000            5          ACTIVE_FLAG      0        'not active user'
7             10000            5          ACTIVE_FLAG      1        'active user'

我需要这个：

ORDER_GROUP_ID  ORDER_ID    OPERATOR_ID GROUP_NAME  VALUE_DESCRIPTION
---------------------------------------------------------------------------------------------
10000           1           3             USER_ID               [not null='panel_id or msisdn_anonym of user']
10000           3           3             MISSING_FLAG          [0='data for extrapolation are not missing'] [1='data for extrapolation are missing']
10000           5           3             PANEL_FLAG            [0='source of user: no panel'] [1='source of user: panel']
10000           7           3             ACTIVE_FLAG           [0='not active user'] [1='active user']
10000           1           5             USER_ID               [not null='panel_id or msisdn_anonym of user']
10000           3           5             MISSING_FLAG          [0='data for extrapolation are not missing'] [1='data for extrapolation are missing']
10000           5           5             PANEL_FLAG            [0='source of user: no panel'] [1='source of user: panel']
10000           7           5             ACTIVE_FLAG           [0='not active user'] [1='active user']

下面是我们的Exadata中使用的当前代码：

SELECT
  order_group_id, order_id, operator_id, group_Name, value_description
FROM 
   (SELECT
        order_id, operator_id, group_Name, level
        , seq, cnt
        , trim (REPLACE (SYS_CONNECT_BY_PATH(value_description, '#'),  '#', ' ' )) AS value_description
        , order_group_id
    FROM 
        (SELECT
            order_id, operator_id, group_Name, VALUE_ID, description, '['||VALUE_ID||'='||description||']' AS value_description
      , row_number() OVER ( PARTITION BY operator_id, group_Name ORDER BY VALUE_ID, description ) seq
            , count(*) OVER ( PARTITION BY operator_id, group_Name ) cnt
            , order_group_id
         FROM 
            NIS_MDM.EPO_FUS_USER_GROUPS_V)
    where level = cnt
    start with seq = 1
    CONNECT BY
    PRIOR operator_id = operator_id and prior group_Name = group_Name
    AND PRIOR seq = seq - 1
  )
  order by operator_id, order_id

我试着使用填充函数，甚至Pivot，但我无法找到一个正确的解决方案。

如果你们能帮我的话我会很高兴的。我觉得我用不同的语言搜索了整个互联网，但没有人解决我的笑脸问题。

Answer 1

你可以试试这个查询。

在SQL Fiddle中检查它

SELECT order_group_id
    ,order_id
    ,operator_id
    ,group_Name
    ,value_description
FROM (
    (
        SELECT order_group_id
            ,order_id
            ,seq = row_number() OVER (
                ORDER BY operator_id
                    ,group_Name
                )
        FROM MyTab
        ) tab INNER JOIN (
        SELECT operator_id
            ,group_Name
            ,row_number() OVER (
                ORDER BY operator_id
                    ,group_Name
                ) seq
            ,STUFF((
                    SELECT ' ' + '[' + VALUE_ID + '=' + [description] + ']'
                    FROM MyTab v
                    WHERE v.operator_id = A.OPERATOR_ID
                        AND v.GROUP_NAME = A.GROUP_NAME
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS value_description
        FROM (
            SELECT order_id
                ,operator_id
                ,group_Name
                ,VALUE_ID
                ,description
                ,order_group_id
            FROM MyTab
            ) a
        GROUP BY operator_id
            ,group_Name
        ) t ON tab.seq = t.seq
    )

Answer 2

我通过创建一个数据库函数来解决这个问题

创建一个像"ufn_GetParentPath"这样的函数，使用它而不是"SYS_CONNECT_BY_PATH"传递order_id作为参数。

CREATE FUNCTION [dbo].[ufn_GetParentPath] ( @pCurrentNodeID    INT )
RETURNS VARCHAR(1000)
  AS
BEGIN

DECLARE @vCurrentNodeName     VARCHAR(50)
DECLARE @vParentID            INT

IF @pCurrentNodeID IS NULL OR @pCurrentNodeID = 0
    RETURN NULL

SELECT @vCurrentNodeName = [VALUE_DESCRIPTION], @vParentID = [operator_id]
FROM [NIS_MDM].[EPO_FUS_USER_GROUPS_V]
WHERE [order_id] = @pCurrentNodeID

RETURN ISNULL([dbo].[ufn_GetParentPath] ( @vParentID ) + '/', '') + @vCurrentNodeName

END
GO