CODEWITHSUNDEEP
sql-servert-sqlsubset
tumultous_rooster
tumultous_rooster

Reputation: 12570

finding values in a non-rectangular t-sql table

This could probably be done easier in Python or R, but I'm doing a SQL Server query on this table:

ID  2011  2012  2013  2014
1   NULL  NULL  NULL   YES
2    NO    NO    YES  NULL
3   YES    NO    YES  NULL
4   YES    NO   NULL  NULL
5   NULL   NO    YES    NO

I'd like to return a result set that consists of rows 1, 2, and 3, since they are rows where the most recent value was YES.

I'm sure there is a good intelligent way to do this, but I'm getting bogged down in too many CASE statements.

Any advice is greatly appreciated.

Upvotes: 0

Views: 60

Answers (2)

Jeroen Mostert
Jeroen Mostert

Reputation: 28799

There are many ways of solving this; this is only one possible approach (which has the benefit of being rather easy to generalize to other questions on the same data). Let T be the table we're interested in.

WITH OrdinalYesNo AS (
    SELECT ID, [YESNO], ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Year] DESC) o
    FROM T UNPIVOT ([YESNO] FOR [Year] IN ([2011], [2012], [2013], [2014])) U
)
SELECT ID
FROM OrdinalYesNo
WHERE o = 1 AND [YESNO] = 'YES'

Upvotes: 1

jpw
jpw

Reputation: 44911

Without using dynamic SQL I think you would have to hard code the columns values and use something like this:

SELECT ID FROM T
WHERE [2014] = 'YES' 
   OR [2013] = 'YES' AND [2014] IS NULL
   OR [2012] = 'YES' AND [2014] IS NULL AND [2013] IS NULL 
   OR [2011] = 'YES' AND [2014] IS NULL AND [2013] IS NULL AND [2012] IS NULL;

-- or this ugly hack if on SQL 2012
SELECT ID FROM T
WHERE RIGHT(CONCAT([2011], [2012], [2013], [2014]),3) = 'YES'

There might be better ways to do this (but I'm half asleep right now and can't think of any).

Sample SQL Fiddle

Upvotes: 0

Related Questions