Geek asks and answers

Find the column name of the last non-zero value in the row

The table is as follows

ID A1 A2 A3 A4 A5 A6 A7 A8 A9
1 YE YE YE NULL YE YE YE NULL NULL
2 YE YE YE NULL NULL NULL NULL NULL NULL
3 YE YE YE YE YE YE YE YE NULL

Where the identifier is the primary key.
I want to get the column name of the last non-zero value in the row, The result looks like this:

ID LAST
1 A7
2 A3
3 A8

Any help on this?

+5
source share
3 answers

Despite my concerns about this scheme, consider the condition of "reverse priority":

select
  id,
  case
    -- first match terminates search
    when A9 is not null then 'A9'
    when A8 is not null then 'A8'
    when A7 is not null then 'A7'
    ..
    else null
  as lastNonNullColumn
from ..

Evaluation order is guaranteed in TSQL (see CASE ), so we just move back :)

Boolean_expression WHEN.

, , UNPIVOT ( ROLLUP [?] UNION). , , .. , , : -)

select
  id,
  max(colName) as lastNonNullColumn
from <<normalized_derived_table>>
where colValue is not null
group by id
+2

? UNPIVOT , , null/blank.

;with cte as
(
  select id
    , last
    , value
    , row_number() over(partition by id order by last) rn
  from
  (
      select id, 
          isnull(a1, '') as a1, 
          isnull(a2, '') as a2, 
          isnull(a3, '') as a3, 
          isnull(a4, '') as a4, 
          isnull(a5, '') as a5, 
          isnull(a6, '') as a6, 
          isnull(a7, '') as a7, 
          isnull(a8, '') as a8, 
          isnull(a9, '') as a9
      from t
  ) x
  unpivot
  (
      value
      for last in (a1, a2, a3, a4, a5, a6, a7, a8, a9)
  ) u
) 
select id, max(last) as last
from cte
where value != ''
group by id

SQL Fiddle with Demo

, :

select id
  , max(last) last
from
(
    select id, a1, a2, a3, a4, a5, a6, a7, a8, a9
    from t
) x
unpivot
(
    value
    for last in (a1, a2, a3, a4, a5, a6, a7, a8, a9)
) u
group by id

. SQL Fiddle with Demo

+2

​​-UNPIVOT , ( ).

SELECT
   T.ID,
   X.Name
FROM
   T
   CROSS APPLY (
      SELECT TOP 1 Name FROM (
         VALUES (1, 'A1', T.A1), (2, 'A2', T.A2), (3, 'A3', T.A3), (4, 'A4', T.A4),
         (5, 'A5', T.A5), (6, 'A6', T.A6), (7, 'A7', T.A7), (8, 'A8', T.A8),
         (9, 'A9', T.A9)
      ) X (Pos, Name, Col)
      WHERE Col IS NOT NULL
      ORDER BY X.Pos DESC
   ) X;

, IO CPU , UNPIVOT ( , ), . CASE, @pst, .

, , UNPIVOT :

SELECT ID, Max(Last)
FROM T UNPIVOT (Value FOR Last IN (A1, A2, A3, A4, A5, A6, A7, A8, A9)) U
GROUP BY ID;

, , , , , :

SELECT
   T.ID,
   Coalesce(
      (SELECT 'A9' WHERE T.A9 IS NOT NULL),
      (SELECT 'A8' WHERE T.A8 IS NOT NULL),
      (SELECT 'A7' WHERE T.A7 IS NOT NULL),
      (SELECT 'A6' WHERE T.A6 IS NOT NULL),
      (SELECT 'A5' WHERE T.A5 IS NOT NULL),
      (SELECT 'A4' WHERE T.A4 IS NOT NULL),
      (SELECT 'A3' WHERE T.A3 IS NOT NULL),
      (SELECT 'A2' WHERE T.A2 IS NOT NULL),
      (SELECT 'A1' WHERE T.A1 IS NOT NULL)
   ) LastNotNullColumn
FROM T
ORDER BY ID

, , CASE, . , , CASE.

, . 15 000 .

Finally, I cannot, with a clear conscience, warn you that your scheme is probably not the best. Although I cannot say that your data, that you are trying to find the latter, probably suggests that the columns represent the time or stages of some kind of life cycle - and this is the wrong database design. Instead, save the data without binding. When the time comes, you need a result set that rotates, you can PIVOT. And, asking for the most recent value for the ID becomes a little easier!

+1
source

More articles:


All Articles