3

I'm trying to write a progressive number in my SQL code that increments every x record, but this x can vary each time. The result that I want looks like this. 

N1 Var Date
1  x1  Date1
1  x2  Date1
1  x3  Date1
2  x1  Date2
2  x3  Date2
3  x2  Date3

Var is a variable that the user can decide to write or leave empty (in that case the variable value in the DB is NULL). The problem is that there is a fixed number of that values that the user can write (in this case 3), but he can decide for each row number to fill x1,x2,x3 or none. In case of None for all Variables I would like to have only one row (just like if the user writes only on one variable). Other problem in this is that the master table of my db that i'm querying has the xs as columns. DFor multiple reasons i had to write them as rows. Also using the N1 is not sufficient for me as (for other reasons) i need to write a progressive that works in that way. x is a NVARCHAR.

N1  Var1  Var2  Var3  Date
1    x1    x2    x3   Date1
2    x1   NULL   x3   Date2
3   NULL   x2   NULL  Date3

I hope i was clear enough. Thanks in advance for your help!

I tried already using ROW_NUMBER() with different combinations of date and x, but without success.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
dimerazor
  • 41
  • 5

2 Answers2

2

Seems you need such an UNPIVOT operation :

 SELECT N1, Var, Date  
   FROM tab 
UNPIVOT ( Var FOR lst_Var IN (var1, var2, var3) ) AS unpvt;

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
2

This is an unpivot operation. However, I much, much prefer using cross apply rather than unpivot. Cross apply implements lateral joins, which are powerful type of join operation, included in the SQL standard. Unpivot is bespoke syntax that has only one use. So:

select t.n1, v.var, t.date
from t cross apply
     (values (Var1), (Var2), (Var3)
     ) v(var)
where var is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786