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
tp3
customer data
Add defination in column while create table
nandhu
Foreign keys
Display Multiple Rows as a single Line
math function
FIGURA5.1
Generate xml
Manish_Sql_updated