How to create a concatenated column if your are getting data from a single column?

Question

I have a column called Issue_Status column, it presents the current status of an Issue raised mostly related in Risk Management. I need to to create a new column in both Cognos and/or SQL Server to present the concatenated columns of all previous statuses of an Issue. It goes like this.

Right now, I don't have the Issue_Status_Concatenated.

It goes like this.

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         

I was thinking of using a running count function (Cognos Function)

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

Then combine the results inside a single column using a case function

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

Then I plan to create a new data item or a new column by creating Issue_Status_Concatenated by combining all New concat columns from 1 to

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

I know this is a long process but I know that there could be an easier and logical way to do this? Is there a way to make this simpler?

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

Below snippet will help you to get the desird output in oracle. Hope this helps.

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
  );