locked
Query returning duplicate rows for cases RRS feed

  • Question

  • Hi

    I've tried so many variations and can't crack this - any ideas/solutions greatly appreciated.

    QUERY

    SELECT EAR.[eno]

    ,EPI.[date]

    ,(case when CGA.[cef] = 'ABC' then CRA.[descrip] else NULL end) as 'App'

    ,(case when CGA.[cef] = 'XYZ' then CRA.[descrip] else NULL end) as 'Act'

    FROM [PROD].[dbo].[ep] EPI

    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]= EAR.[eno]

    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]

    WHERE EPI.[type]='0000'

    GROUP B EAR.[eno], EPI.[date], CRA.[descrip]

    Desired output

    Eno    date                      App             Act

    325         01/01/2018                         sql                       coder

    318         05/10/2018                         ms word              data entry

     

    Current output

    Eno    date                      App            Act

    325         01/01/2018                         sql                     null

    325         01/01/2018                         null                    coder

    318         05/10/2018                         ms word            null

    318              05/10/2018                                   null                         data entry
    Tuesday, September 25, 2018 10:00 AM

Answers

  • With Cte as 

    (

    SELECT EAR.[eno]

    ,EPI.[date]

    ,(case when CGA.[cef] = 'ABC' then CRA.[descrip] else NULL end) as 'App'

    ,(case when CGA.[cef] = 'XYZ' then CRA.[descrip] else NULL end) as 'Act'

    FROM [PROD].[dbo].[ep] EPI

    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]EAR.[eno]

    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]

    WHERE EPI.[type]='0000'

    GROUP BY EAR.[eno], EPI.[date], CRA.[descrip])

    Select Maeno, date, max(app), max(act) from cte group by eno, [date]

    Please mark as answer if this post helped you

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, September 25, 2018 11:54 AM
    • Marked as answer by Melissa.au Tuesday, September 25, 2018 12:14 PM
    Tuesday, September 25, 2018 10:07 AM

All replies

  • With Cte as 

    (

    SELECT EAR.[eno]

    ,EPI.[date]

    ,(case when CGA.[cef] = 'ABC' then CRA.[descrip] else NULL end) as 'App'

    ,(case when CGA.[cef] = 'XYZ' then CRA.[descrip] else NULL end) as 'Act'

    FROM [PROD].[dbo].[ep] EPI

    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]EAR.[eno]

    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]

    WHERE EPI.[type]='0000'

    GROUP BY EAR.[eno], EPI.[date], CRA.[descrip])

    Select Maeno, date, max(app), max(act) from cte group by eno, [date]

    Please mark as answer if this post helped you

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, September 25, 2018 11:54 AM
    • Marked as answer by Melissa.au Tuesday, September 25, 2018 12:14 PM
    Tuesday, September 25, 2018 10:07 AM
  • You could evaluate App and Act values first and then apply GROUP BY on top of it.

    Also, specifying NULL in else condition could be removed.

    ;with cte
    as(
    SELECT EAR.[eno]
    
    ,EPI.[date]
    
    ,(case when CGA.[cef] = 'ABC' then CRA.[descrip] end) as 'App'
    
    ,(case when CGA.[cef] = 'XYZ' then CRA.[descrip] end) as 'Act'
    
    FROM [PROD].[dbo].[ep] EPI
    
    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]= EAR.[eno]
    
    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]
    
    WHERE EPI.[type]='0000'
    )
    select [eno],[date], max(App) ,max(Act) from cte group by [eno],[date]


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, September 25, 2018 10:25 AM
  • you can do this in two ways

    1. using classic crosstab

    like

    SELECT EAR.[eno]
    
    ,EPI.[date]
    
    ,max(case when CGA.[cef] = 'ABC' then CRA.[descrip] else NULL end) as 'App'
    
    ,max(case when CGA.[cef] = 'XYZ' then CRA.[descrip] else NULL end) as 'Act'
    
    FROM [PROD].[dbo].[ep] EPI
    
    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]= EAR.[eno]
    
    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]
    
    WHERE EPI.[type]='0000'
    
    GROUP B EAR.[eno], EPI.[date]

    2. using pivot

    SELECT [eno]
    ,[date]
    ,[ABC] as 'App'
    ,[XYZ] as 'Act'
    FROM
    (
    SELECT EAR.[eno]
    ,EPI.[date]
    ,CGA.[cef]
    ,CRA.[descrip]
    FROM [PROD].[dbo].[ep] EPI
    
    LEFT JOIN [PROD].[dbo].[ear] EAR ON EPI.[eno]= EAR.[eno]
    
    LEFT JOIN [PROD].[dbo].[ceg] CGA ON CRA.[cef] = CGA.[cef]
    
    WHERE EPI.[type]='0000'
    )t
    PIVOT (MAX([descrip]) FOR [cef] IN ([ABC],[XYZ]))p
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 25, 2018 10:30 AM
  • Thank you so much, perfect!
    Tuesday, September 25, 2018 11:36 AM
  • awesome, thank you, great minds think alike as the previous person posted same.
    Tuesday, September 25, 2018 11:37 AM
  • Thank you, I will have a play with those options, much appreciated.
    Tuesday, September 25, 2018 11:39 AM