如果要从单个列获取数据，如何创建级联列?

Question

我有一个名为Issue_Status列的列，它显示了风险管理中提出的问题的当前状态。我需要在 Cognos 和/或 SQL Server 中创建一个新列，以显示问题的所有先前状态的串联列。事情是这样的。

现在，我没有Issue_Status_Concatenated。

事情是这样的。

Issue I.D   Issue Status   
1234        Draft           
1234        Open           
1234        Closed          
5678        Draft           
5678        Pending         
5678        Closed          
5678        Cancelled       
3333        Draft           
3333        Pending         
3333        Pending         
3333        Pending         

我正在考虑使用运行计数函数(Cognos 函数(

Issue I.D   Issue Status         Running_Count
1234        Draft                1
1234        Open                 2
1234        Closed               3
5678        Draft                1
5678        Pending              2
5678        Closed               3
5678        Cancelled            4
3333        Draft                1
3333        Pending              2
3333        Pending              3
3333        Pending              4

然后使用案例函数将结果合并到一列中

New Concat_Column 1
case when Running_Count = 1 then Issue_status else ' ' end
New Concat_Column 2 
case when Running_Count = 2 then Issue_status else ' ' end
New Concat_Column 3 
case when Running_Count = 3 then Issue_status else ' ' end
New Concat_Column 4
case when Running_Count = 4 then Issue_status else ' ' end

然后，我计划通过Issue_Status_Concatenated将所有新 concat 列从 1 组合

到

New Concat_Column 1 + '|' + New Concat_Column  2 + '|' + New Concat_Column 3 + '|' + New Concat_Column 4

我知道这是一个漫长的过程，但我知道可能有一种更简单和合乎逻辑的方法可以做到这一点？有没有办法让这更简单？

Issue I.D   Issue Status   Issue_Status_Concatenated
1234        Draft           Draft | Open | Closed
1234        Open            Draft | Open | Closed
1234        Closed          Draft | Open | Closed
5678        Draft           Draft | Open | Closed |Cancelled
5678        Pending         Draft | Open | Closed |Cancelled
5678        Closed          Draft | Open | Closed |Cancelled
5678        Cancelled       Draft | Open | Closed |Cancelled
3333        Draft           Draft | Pending
3333        Pending         Draft | Pending
3333        Pending         Draft | Pending
3333        Pending         Draft | Pending

Answer 1

下面的代码片段将帮助您在 oracle 中获取所需的输出。希望这有帮助。

SELECT ID,
STAT,
LISTAGG(STAT,'|') WITHIN GROUP(
ORDER BY STAT) OVER(PARTITION BY ID) AGG_STAT
FROM
(WITH TMP AS
( SELECT 1234 id, 'Draft' Stat FROM dual
UNION ALL
SELECT 1234 ID, 'Open' STAT FROM DUAL
UNION ALL
SELECT 1234 id, 'Completed' Stat FROM dual
UNION ALL
SELECT 1100 ID, 'Draft' STAT FROM DUAL
UNION ALL
SELECT 1100 ID, 'Pending' STAT FROM DUAL
UNION ALL
SELECT 1100 ID, 'Completed' STAT FROM DUAL
UNION ALL
SELECT 1100 id, 'Closed' Stat FROM dual
)
SELECT tmp.*,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 1 DESC) RN FROM tmp
);

Answer 2

如果它的SQL服务器

SELECT  i.issue_id,
stuff((
SELECT DISTINCT ' | ' + issue_status
FROM issues i1
WHERE i1.issue_id = i.issue_id for xml path('')),1,3,'')
FROM issues i 

如果它的神谕可能沿着 Listagg 的路线 https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

Answer 3

对于 SQL 服务器，您可以使用以下查询来获取结果 -

Create table #IssueLog (
[Issue I.D] int ,
[Issue Status] varchar(10)
)
insert into #IssueLog
select 1234 ,       'Draft'
union all  select 1234,     'Open'           
union all  select 1234,      'Closed'          
union all  select 5678,     'Draft'           
union all  select 5678,      'Pending'         
union all  select 5678,     'Closed'          
union all  select 5678,     'Cancelled'       
union all  select 3333,     'Draft'           
union all  select 3333,     'Pending'         
union all  select 3333,     'Pending'         
union all  select 3333,      'Pending'   

Select Main.[Issue I.D],
Left(Main.[Issues],Len(Main.[Issues])-1) As [Issues]
From
(
Select distinct T2.[Issue I.D] ,
(
Select T1.[Issue Status] + '|' AS [text()]
From #IssueLog T1
Where T1.[Issue I.D] = T2.[Issue I.D]
ORDER BY T1.[Issue I.D]
For XML PATH ('')
) [Issues]
From #IssueLog T2
) [Main]