Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
generate-data-combinations-for-a-column-dynamically-based-on-another-column-sql
--Sql Server 2014 Express Edition --Batches are separated by 'go' with a as ( select 101 as col1, 'A' as col2 union all select 102, 'B' union all select 102, 'C' union all select 102, 'D' union all select 103, 'C' union all select 103, 'E' -- union all select 101, 'Z' ), b as ( select col1, col2, count(*) over() as ct, count(*) over(partition by col1) as cc1, dense_rank() over(order by col1 desc) as rk1, row_number() over(partition by col1 order by col2) as rn12 from a ), r as ( select col1, col2, ct / cc1 as rq, ct / cc1 as ll, cc1, rk1, rn12 from b union all select col1, col2, rq, ll - 1, cc1, rk1, rn12 from r where ll > 1 ) select iif(rk1 = 1, (ll - 1) * cc1 + rn12, (rn12 - 1) * rq + ll) as id, col1, col2 from r order by id, col1, col2 option (maxrecursion 0);
run
|
edit
|
history
|
help
0
NAMES
Co-Related sub query
string splitter
Joins outside XML element
Insert index into Temporary Table
#Temp Table fill
20181CSE0534
FETCH THE WEEKDAYS DATA
table variables (temporary tables)
db3