(O.P.'s note: preferred solution is the 4th / final code block)
XML seems to me to be the obvious choice of data structure to use here.
with N as
(
select T.N
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
(12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select top(5 + abs(checksum(newid())) % 15)
N1.N as '@Value',
(
select top(1 + abs(checksum(newid())) % 10)
N2.N as '@Value',
(
select top(1 + abs(checksum(newid())) % 5)
N3.N as '@Value'
from N as N3
where N2.N > 0
for xml path('Level3'), type
)
from N as N2
where N1.N > 0
for xml path('Level2'), type
)
from N as N1
for xml path('Level1'), root('Root');
The trick to make SQL Server use different values for top()
for each node is to make the sub-queries correlated. N1.N > 0
and N2.N > 0
.
Flatteing the XML:
declare @X xml;
with N as
(
select T.N
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
(12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select @X = (
select top(5 + abs(checksum(newid())) % 15)
N1.N as '@Value',
(
select top(1 + abs(checksum(newid())) % 10)
N2.N as '@Value',
(
select top(1 + abs(checksum(newid())) % 5)
N3.N as '@Value'
from N as N3
where N2.N > 0
for xml path('Level3'), type
)
from N as N2
where N1.N > 0
for xml path('Level2'), type
)
from N as N1
for xml path('Level1')
);
select L1.X.value('@Value', 'varchar(10)')+'\'+
L2.X.value('@Value', 'varchar(10)')+'\'+
L3.X.value('@Value', 'varchar(10)')
from @X.nodes('/Level1') as L1(X)
cross apply L1.X.nodes('Level2') as L2(X)
cross apply L2.X.nodes('Level3') as L3(X);
And a version totally void of XML.
with N as
(
select T.N
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
(12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select cast(N1.N as varchar(10))+'\'+
cast(N2.N as varchar(10))+'\'+
cast(N3.N as varchar(10))
from (
select top(5 + abs(checksum(newid())) % 15)
N.N
from N
) as N1
cross apply
(
select top(1 + abs(checksum(newid())) % 10)
N.N
from N
where N1.N > 0
) as N2
cross apply
(
select top(1 + abs(checksum(newid())) % 5)
N.N
from N
where N2.N > 0
) as N3;
Correlation N1.N > 0
and N2.N > 0
is still important.
A version using a table with 20 names to be used instead of just integers.
declare @Elements table
(
Name nvarchar(50) not null
);
insert into @Elements(Name)
select top(20) C.name
from sys.columns as C
group by C.name;
select N1.Name + N'\' + N2.Name + N'\' + N3.Name
from (
select top(5 + abs(checksum(newid())) % 15)
E.Name
from @Elements as E
) as N1
cross apply
(
select top(1 + abs(checksum(newid())) % 10)
E.Name
from @Elements as E
where N1.Name > ''
) as N2
cross apply
(
select top(1 + abs(checksum(newid())) % 5)
E.Name
from @Elements as E
where N2.Name > ''
) as N3;
Best Answer