Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Combining multiple rows to one row

    I'm attempting to combine multiple names across rows to one column when the project_id matches. I started with a self join but could not get it to work the way I wanted it to and I'm pretty certain there is a function or cte that can do this a lot easier. Asking for direction. Working with db2.

    Here is what I have so far which doesnt work and is producing a -104 error. I have also attached a copy of the data and expected outcome. Please help.

    Code:
    (
    
            SELECT 
                            DP.D_P_ID, DP.project_name,
                            DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST AS 
                            FROM Fact_table as FAT 
                            INNER JOIN D_P DP ON FAT.D_P_ID = DP.D_P_ID
                            INNER JOIN B_U_P BUP on DP.D_P = BUP.D_P_ID
                            INNER JOIN D_U DU2 ON BUP.D_U_ID = DU2.D_U_ID
                            INNER JOIN D_Date DD ON FAT.START_DATE_ID = DD.DATE_KEY
                            INNER JOIN D_A DA ON FAT.D_A_ID = DA.D_A_ID
                            WHERE  ((    (DD.DATE_VALUE >= '2013-01-01')
                                    OR (DD.DATE_VALUE < '2014-01-01')
                                    OR (DD.DATE_VALUE <= '2013-01-01')))
                                    AND DA.M_NAME = 'Mandy'
                                    AND BUP.USER_FLAG = 'Y'
    
                            GROUP BY  DP.D_P_ID, DP.project_name, DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST
                            ORDER BY DP.project_name 
                 )  PI1      
    
                     join 
    
                            (
                                    SELECT 
                            DP.D_P_ID, DP.project_name,
                            DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST AS 
                            FROM Fact_table as FAT 
                            INNER JOIN D_P DP ON FAT.D_P_ID = DP.D_P_ID
                            INNER JOIN B_U_P BUP on DP.D_P = BUP.D_P_ID
                            INNER JOIN D_U DU2 ON BUP.D_U_ID = DU2.D_U_ID
                            INNER JOIN D_Date DD ON FAT.START_DATE_ID = DD.DATE_KEY
                            INNER JOIN D_A DA ON FAT.D_A_ID = DA.D_A_ID
                            WHERE  ((    (DD.DATE_VALUE >= '2013-01-01')
                                    OR (DD.DATE_VALUE < '2014-01-01')
                                    OR (DD.DATE_VALUE <= '2013-01-01')))
                                    AND DA.M_NAME = 'Mandy'
                                    AND BUP.USER_FLAG = 'Y'
    
                            GROUP BY  DP.D_P_ID, DP.project_name, DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST
                            ORDER BY DP.project_name 
                            ) PI2 on PI1.d_p_id = PI2.d_p_id
    Attached Thumbnails Attached Thumbnails Data.png   Expected_Data_outcome.png  

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    This comes pretty close to your expected output.

    Included is the DDL & Insert statements from your example.

    Code:
    declare @t1 table
    (
       d_p_id   int,
       project_name char(30),
       admin_name char(30)
    )
    
    insert @t1 (d_p_id, project_name, admin_name)   values
      (1000, 'Redbull', 'Mosely, Susan')
     ,(1002, 'Gatorade', 'Doe, John')
     ,(1002, 'Gatorade', 'Hue, Amber')
     ,(1040, 'Vitamin Water', 'Jones, Michael')
     ,(1040, 'Vitamin Water', 'Half, Robert')
     ,(1040, 'Vitamin Water', 'Damon, Tyler')
     ,(1040, 'Vitamin Water', 'Cox, Richard')
    The query:

    Code:
       ;with cte as
       (
          select d_p_id, project_name, admin_name,
             Row_Number() over (order by d_p_id, project_name, admin_name) RowNum
             from @t1
       )
       Select case when c.d_p_id = p.d_p_id then '' else cast(c.d_p_id as varchar(4)) end ID, 
              case when c.d_p_id = p.d_p_id then '' else c.project_name end Project, 
              c.admin_name Admin
         from cte c
         left outer join cte as p
           on c.RowNum = p.RowNum + 1
    Here is the output I got:

    Code:
    1000	Redbull                       	Mosely, Susan                 
    1002	Gatorade                      	Doe, John                     
     		                        Hue, Amber                    
    1040	Vitamin Water                 	Cox, Richard                  
    		                        Damon, Tyler                  
    		                        Half, Robert                  
    		                        Jones, Michael
    Last edited by LinksUp; 08-16-13 at 23:41.

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    Thank you for your help. I'm having a problem with the case statement. It errors out at C.D_p_id (SQLCODE= -206, SQLSTATE=42703). What am I doing wrong?

    Code:
     ;with cte as
       (
          select DP.D_P_ID as project_id, DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST as PI,
             Row_Number() over (order by DP.D_P_ID, DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST) RowNum
             from Fact_table as FAT
            INNER JOIN D_P DP ON FAT.D_P_ID = DP.D_P_ID
            INNER JOIN B_U_P BUP on DP.D_P_ID = BUP.D_P_ID
            INNER JOIN DIM_USER DU2 ON BUP.D_U_ID = DU2.D_U_ID
            INNER JOIN DIM_DATE DD ON FAT.ALLOCATION_START_DATE_DIM_ID = DD.DATE_KEY
            INNER JOIN D_A DA ON FAT.D_A_ID = DA.DIM_A_ID
            WHERE  ((    (DD.DATE_VALUE >= '2013-01-01')
            OR (DD.DATE_VALUE < '2014-01-01')
            OR (DD.DATE_VALUE <= '2013-01-01')))
            AND DA.M_NAME = 'mandy'
            AND BUP.PI_FLAG = 'Y'
            GROUP BY DP.D_P_ID , DU2.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST
       ) 
       Select case when c.D_P_ID  = p.D_P_ID 
                   then 0 
                   else c.D_P_ID 
                   end ID,
               case when c.DIM_PROJECT_ID = p.D_P_ID 
                   then '' 
                   else c.D_P_ID 
                   end Project, c.NAME_LAST CONCAT ', ' CONCAT DU2.NAME_FIRST
                   
         from cte c
         left outer join cte as p
           on c.RowNum = p.RowNum + 1

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You specified an alias for D_P_ID, like
    Code:
    ;with cte as
       (
          select DP.D_P_ID as project_id, ...
    ...
    So, you sould use the alias(i.e. project_id) instead of column name(i.e. D_P_ID).

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If OVER(PARTITION BY d_p_id ...) was used,
    it may be not neccesary to join the cte.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*****************************************************
    *** The cte was used to generate test data easily. ***
    *****************************************************/
     test_data
    ( d_p_id , project_name , name_last , name_first ) AS (
    VALUES
      (1000 , 'Redbull'       , 'Mosely' , 'Susan'   )
    , (1002 , 'Gatorade'      , 'Doe'    , 'John'    )
    , (1002 , 'Gatorade'      , 'Hue'    , 'Amber'   )
    , (1040 , 'Vitamin Water' , 'Jones'  , 'Michael' )
    , (1040 , 'Vitamin Water' , 'Half'   , 'Robert'  )
    , (1040 , 'Vitamin Water' , 'Damon'  , 'Tyler'   )
    , (1040 , 'Vitamin Water' , 'Cox'    , 'Richard' )
    )
    SELECT CASE row_num
           WHEN 1 THEN CHAR(d_p_id)
           ELSE        ''
           END  AS project_id
         , CASE row_num
           WHEN 1 THEN project_name
           ELSE        ''
           END  AS project_name
         , name_last || ', ' || name_first AS admin
     FROM  (SELECT d_p_id , project_name
                 , name_last , name_first
                 , ROW_NUMBER()
                      OVER( PARTITION BY d_p_id
                                ORDER BY name_last
                                       , name_first ) AS row_num
             FROM  test_data
           ) AS s
     ORDER BY
           d_p_id
         , row_num
    ;
    ------------------------------------------------------------------------------
    
    PROJECT_ID  PROJECT_NAME  ADMIN          
    ----------- ------------- ---------------
    1000        Redbull       Mosely, Susan  
    1002        Gatorade      Doe, John      
                              Hue, Amber     
    1040        Vitamin Water Cox, Richard   
                              Damon, Tyler   
                              Half, Robert   
                              Jones, Michael 
    
      7 record(s) selected.

  6. #6
    Join Date
    Aug 2013
    Posts
    14
    Thank you both for your responses. I was able to take that advice and produce what I needed. I did have a question on how to filter the results of the list. Once you have concatenated the results of multiple rows and produced a list how would i filter the admin_name where no duplicates are listed. For example if I had a duplicate for d_p_id =1002, project_name ='Gatorade', admin_name ='John Doe'. If admin John Doe was listed twice how do I make that distinct within the list and display the name once?
    Last edited by wtolbert; 08-20-13 at 16:58. Reason: edit

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about using RANK() and DISTINCT in a subquery, like...
    Code:
    WITH
     test_data
    ( d_p_id , project_name , name_last , name_first ) AS (
    VALUES
      (1000 , 'Redbull'       , 'Mosely' , 'Susan'   )
    , (1002 , 'Gatorade'      , 'Doe'    , 'John'    )
    , (1002 , 'Gatorade'      , 'Doe'    , 'John'    )
    , (1002 , 'Gatorade'      , 'Hue'    , 'Amber'   )
    , (1040 , 'Vitamin Water' , 'Jones'  , 'Michael' )
    , (1040 , 'Vitamin Water' , 'Half'   , 'Robert'  )
    , (1040 , 'Vitamin Water' , 'Half'   , 'Robert'  )
    , (1040 , 'Vitamin Water' , 'Damon'  , 'Tyler'   )
    , (1040 , 'Vitamin Water' , 'Cox'    , 'Richard' )
    , (1040 , 'Vitamin Water' , 'Half'   , 'Robert'  )
    )
    SELECT CASE rank_name
           WHEN 1 THEN CHAR(d_p_id)
           ELSE        ''
           END  AS project_id
         , CASE rank_name
           WHEN 1 THEN project_name
           ELSE        ''
           END  AS project_name
         , name_last || ', ' || name_first AS admin
     FROM  (SELECT DISTINCT
                   d_p_id , project_name
                 , name_last , name_first
                 , RANK()
                      OVER( PARTITION BY d_p_id
                                ORDER BY name_last
                                       , name_first ) AS rank_name
             FROM  test_data
           ) AS s
     ORDER BY
           d_p_id
         , rank_name
    ;
    Note1:The cte test_data was used to generate test data easily.
    Note2: Duplicated rows were marked by Red.


    Results were...
    Code:
    PROJECT_ID  PROJECT_NAME  ADMIN          
    ----------- ------------- ---------------
    1000        Redbull       Mosely, Susan  
    1002        Gatorade      Doe, John      
                              Hue, Amber     
    1040        Vitamin Water Cox, Richard   
                              Damon, Tyler   
                              Half, Robert   
                              Jones, Michael 
    
      7 record(s) selected.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •