Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
generate-data-combinations-for-a-column-dynamically-based-on-another-c...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--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);
View schema
Execution time: 0 sec, rows selected: 18, rows affected: 0, absolute service time: 0,17 sec, absolute service time: 0,2 sec
edit mode
|
history
|
discussion
id
col1
col2
1
1
101
A
2
1
102
B
3
1
103
C
4
2
101
A
5
2
102
B
6
2
103
E
7
3
101
A
8
3
102
C
9
3
103
C
10
4
101
A
11
4
102
C
12
4
103
E
13
5
101
A
14
5
102
D
15
5
103
C
16
6
101
A
17
6
102
D
18
6
103
E