CODEWITHSUNDEEP
sqlsql-serverunpivotrow-number
dimerazor
dimerazor

Reputation: 41

How to make a progressive in SQL that increments each x records where x can potentially be different for each row?

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.

Upvotes: 3

Views: 356

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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;

Upvotes: 2

Related Questions