SQL - Flattening a table using windowing and case statements

Question

First time asker -- I'm having some problems combining case logic and windowing in SqlServer 2012. I need to flatten the data structure shown below, so I'll be running MAX statements against these results afterwards. I'm using case/when logic to specify an 'Owner' for each xID. My challenge is I'm consistently getting the wrong results and I haven't been able to determine what I'm doing wrong.

Here is the case/when logic and the table structure. For the 'Owner' column I need to evaluate three conditions and was trying to use FIRST_VALUE() to select the output of the case statement. The logic in the case statement works correctly, but when I try to apply the value to each of the xIDs using the windowing function it's returning the wrong value.

FIRST_VALUE(case
when [partnerType] = 'Giver' and [partnerAgree] = 'True' then [partnerGroup]
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then [partnerGroup]
when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then [partnerGroup]
else Null end) over (partition by [xID] order by [yID])
as 'Owner'

Desired Results --------------------------

|xID|yID| Owner     |partnerType| partnergrp|partnerAgree
|100|  1| grp_Banana|Taker      |grp_Apple  |TRUE
|100|  2| grp_Banana|Giver      |grp_Banana |TRUE
|100|  3| grp_Banana|Taker      |grp_Banana |FALSE
|101|  1| grp_Carrot|Taker      |grp_Carrot |TRUE
|101|  2| grp_Carrot|Giver      |grp_Danish |FALSE
|101|  3| grp_Carrot|Taker      |grp_Banana |TRUE
|101|  4| grp_Carrot|Taker      |grp_Danish |FALSE

Results I'm getting --------------------------

|xID|yID| Owner     |partnerType| partnergrp|partnerAgree
|100|  1| grp_Apple |Taker      |grp_Apple  |TRUE
|100|  2| grp_Apple |Giver      |grp_Banana |TRUE
|100|  3| grp_Apple |Taker      |grp_Banana |FALSE
|101|  1| grp_Carrot|Taker      |grp_Carrot |TRUE
|101|  2| grp_Carrot|Giver      |grp_Danish |FALSE
|101|  3| grp_Carrot|Taker      |grp_Banana |TRUE
|101|  4| grp_Carrot|Taker      |grp_Danish |FALSE

The first table shows the results as I would expect them, but the code produces the values in the second table. Example: for xID=100 I would expect the Owner to be grp_Banana, but my code returns grp_Apple. For xID=101 I'm getting the correct answer, but for the wrong reason. The windowing function seems to be taking the first yID for any result set.

Thanks, any help is appreciated. Also, I'm open to not using a windowing function it just seem the right direction.

Welcome to Staackoverflow! I think it would very helpful if we could see the output of your current case statement. You provided your expected results, which is good. Adding some sample data can also be useful. — MhQ-6, Mar 25 '19 at 18:59
@BrianBlanton . . . Please describe the logic for `owner`. You show code that *doesn't* work, but don't explain what you really want anywhere. Also, a database tag is also helpful. — Gordon Linoff, Mar 25 '19 at 19:01
Which dbms??? SQL is just a language used by multiple dbms vendors. — Eric, Mar 25 '19 at 19:10
Thanks for the feedback, added additional detail to the description. — Brian Blanton, Mar 25 '19 at 20:18

score 2 · Accepted Answer · answered Mar 25 '19 at 19:08

This is tricky unless your database supports the ignore nulls argument. You can do this with two window functions:

max(case when yid = yid_special then partnerGroup end) over (partition by xid) as Owner
from (select . . . ,
             min(case when partnerType = 'Giver' and [partnerAgree] = 'True' then yid
                      when partnerType = 'Impacted' and [partnerAgree] = 'True' then yid
                      when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then yid
                 end) over (partition by xid) as yid_special

You may also be able to write this using first_value():

first_value(partnerGroup) over
    (partition by xid
     order by (case when partnerType = 'Giver' and [partnerAgree] = 'True' then yid
                    when partnerType = 'Impacted' and [partnerAgree] = 'True' then yid
                    when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then yid
                    else 999999
                end) 
    ) as owner

Thanks @Gordon-Linoff I used your second suggestion as the basis for solving my problem. I don't think I would have solved it so quickly without your feedback. I also added your blog to my reading list. — Brian Blanton, Mar 26 '19 at 15:32

score 0 · Answer 2 · answered Mar 26 '19 at 15:35

Using the suggestion from @Gordon-Linoff I was able to solve my problem. Here's the modified code. Seeing Gordon's use of the case statement in the Order By made me realize why my code was sometimes selecting the wrong values. I credit him with the answer since I just built on his approach. Much appreciated.

, FIRST_VALUE(
case
when [partnerType] = 'Remediator' and [partnerAgree] = 'True' then [partnerGroup]
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then [partnerGroup]
when [yID] = 1 and [partnerAgree] <> 'True' then [partnerGroup]
else [partnerGroup] end) 
over 
(partition by [Incident ID] order by
case 
when [partnerType] = 'Remediator' and [partnerAgree] = 'True' then 1
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then 2
when [yID] = 1 and [partnerAgree] <> 'True' then 3   
else 100
end
)
as 'Owner'

SQL - Flattening a table using windowing and case statements

2 Answers2