Skip to main content
compact solution further
Source Link

T-SQL, (981 9701,168 1,168139 bytes)

 WITH g AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM g WHERE n+1<61),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM g),NP ASas(SELECT 0 AS n UNION ALL SELECT n+1* FROMfrom N(values(1), WHERE(2), n+1<=5(4),P as(SELECT8), power(216),n (32)) as Q(p FROM N)),S as(select distinct p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select * from S where e>=p and e<61),R as(select p,(row_number()over(partition by p order by cast(e as int)))%5 as r,e from D),H as(select k.p,'['+stuff((select','+cast(l.e as varchar)from R l where l.p=k.p and l.r=k.r for xml path('')),1,1,'')+']'as s from R k group by k.p,k.r)select stuff((select','+cast(x.s as varchar)from H x where x.p=z.p for xml path('')),1,1,'')from H z group by z.p
WITH gen -- numbers 1 to 60
AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=60
),
BINARIES -- string representations of binaries 000001 through 111111
as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
NPOWERS -- numbers 0 through 5
AS (
   first SELECT6 0powers ASof n2
    UNIONas ALL(
    SELECT n+1 FROM N* WHEREfrom n+1<=5
(values(1),
POWERS -- powers of 2 based on N (2 to the power of zero), through 2 to the power of 5(4)
as, (
SELECT
power8), (216),n (32)) as Q(powr FROM N)
),
SETELEMENTS -- cross apply the six powers of 2 against the binaries
-- returns 2 cols. col 1 = the power of 2 in question.
-- col 2 is calculated as that power of 2 plus the sum of each power of 2 other than the current row's power value, 
-- but only where a given power of 2 is switched "on" in the binary string, 
-- ie. where the first digit in the string represents 32, the second represents 16 and so on. 
-- That is, if the binary is 100100 then the number will be 
-- the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) 
-- but if the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
-- rows are distinct.
as (
select distinct powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS -- purge calculated numbers smaller than the power of 2 or greater than 60
as (
select * from SETELEMENTS where elt >= powr and elt < 61
)--,
,
ROWNUMBERED -- for each power, number the rows repeatedly from 0 through 5, then back to 0 through 5 again, etc
as (
select powr, (row_number() over (partition by powr order by cast(elt as int)))%5 as r, elt  from DISTINCTELEMENTS
),
GROUPEDSETS -- for each row number, within each power, aggregate the numbers as a comma-delimited list and wrap in square brackets - the inner arrays
as (
select r1.powr, '['+stuff((select ',' + cast(r2.elt as varchar) from ROWNUMBERED r2 where r2.powr = r1.powr and r2.r = r1.r for xml path('')),1,1,'')+']' as s
from ROWNUMBERED r1
group by r1.powr,r1.r
)
select -- now aggregate all the inner arrays per power
stuff((select ',' + cast(g2.s as varchar) from GROUPEDSETS g2 where g2.powr = g1.powr for xml path('')),1,1,'')
from GROUPEDSETS g1
group by g1.powr

Shorter to hard-code arrays?

Yes.

Byte me.

T-SQL, (981 970 1,168 bytes)

 WITH g AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM g WHERE n+1<61),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM g),N AS(SELECT 0 AS n UNION ALL SELECT n+1 FROM N WHERE n+1<=5),P as(SELECT power(2,n)as p FROM N),S as(select distinct p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select * from S where e>=p and e<61),R as(select p,(row_number()over(partition by p order by cast(e as int)))%5 as r,e from D),H as(select k.p,'['+stuff((select','+cast(l.e as varchar)from R l where l.p=k.p and l.r=k.r for xml path('')),1,1,'')+']'as s from R k group by k.p,k.r)select stuff((select','+cast(x.s as varchar)from H x where x.p=z.p for xml path('')),1,1,'')from H z group by z.p
WITH gen -- numbers 1 to 60
AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=60
),
BINARIES -- string representations of binaries 000001 through 111111
as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
N -- numbers 0 through 5
AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM N WHERE n+1<=5
),
POWERS -- powers of 2 based on N (2 to the power of zero, through 2 to the power of 5)
as (
SELECT
power(2,n) as powr FROM N
),
SETELEMENTS -- cross apply the six powers of 2 against the binaries
-- returns 2 cols. col 1 = the power of 2 in question.
-- col 2 is calculated as that power of 2 plus the sum of each power of 2 other than the current row's power value, 
-- but only where a given power of 2 is switched "on" in the binary string, 
-- ie. where the first digit in the string represents 32, the second represents 16 and so on. 
-- That is, if the binary is 100100 then the number will be 
-- the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) 
-- but if the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
-- rows are distinct.
as (
select distinct powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS -- purge calculated numbers smaller than the power of 2 or greater than 60
as (
select * from SETELEMENTS where elt >= powr and elt < 61
)--,
,
ROWNUMBERED -- for each power, number the rows repeatedly from 0 through 5, then back to 0 through 5 again, etc
as (
select powr, (row_number() over (partition by powr order by cast(elt as int)))%5 as r, elt  from DISTINCTELEMENTS
),
GROUPEDSETS -- for each row number, within each power, aggregate the numbers as a comma-delimited list and wrap in square brackets - the inner arrays
as (
select r1.powr, '['+stuff((select ',' + cast(r2.elt as varchar) from ROWNUMBERED r2 where r2.powr = r1.powr and r2.r = r1.r for xml path('')),1,1,'')+']' as s
from ROWNUMBERED r1
group by r1.powr,r1.r
)
select -- now aggregate all the inner arrays per power
stuff((select ',' + cast(g2.s as varchar) from GROUPEDSETS g2 where g2.powr = g1.powr for xml path('')),1,1,'')
from GROUPEDSETS g1
group by g1.powr

T-SQL, (1,168 1,139 bytes)

 WITH g AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM g WHERE n+1<61),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM g),P as(SELECT * from (values(1), (2), (4), (8), (16), (32)) as Q(p)),S as(select distinct p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select * from S where e>=p and e<61),R as(select p,(row_number()over(partition by p order by cast(e as int)))%5 as r,e from D),H as(select k.p,'['+stuff((select','+cast(l.e as varchar)from R l where l.p=k.p and l.r=k.r for xml path('')),1,1,'')+']'as s from R k group by k.p,k.r)select stuff((select','+cast(x.s as varchar)from H x where x.p=z.p for xml path('')),1,1,'')from H z group by z.p
WITH gen -- numbers 1 to 60
AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=60
),
BINARIES -- string representations of binaries 000001 through 111111
as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
POWERS -- first 6 powers of 2
as (
SELECT * from (values(1), (2), (4), (8), (16), (32)) as Q(powr)
),
SETELEMENTS -- cross apply the six powers of 2 against the binaries
-- returns 2 cols. col 1 = the power of 2 in question.
-- col 2 is calculated as that power of 2 plus the sum of each power of 2 other than the current row's power value, 
-- but only where a given power of 2 is switched "on" in the binary string, 
-- ie. where the first digit in the string represents 32, the second represents 16 and so on. 
-- That is, if the binary is 100100 then the number will be 
-- the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) 
-- but if the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
-- rows are distinct.
as (
select distinct powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS -- purge calculated numbers smaller than the power of 2 or greater than 60
as (
select * from SETELEMENTS where elt >= powr and elt < 61
)--,
,
ROWNUMBERED -- for each power, number the rows repeatedly from 0 through 5, then back to 0 through 5 again, etc
as (
select powr, (row_number() over (partition by powr order by cast(elt as int)))%5 as r, elt  from DISTINCTELEMENTS
),
GROUPEDSETS -- for each row number, within each power, aggregate the numbers as a comma-delimited list and wrap in square brackets - the inner arrays
as (
select r1.powr, '['+stuff((select ',' + cast(r2.elt as varchar) from ROWNUMBERED r2 where r2.powr = r1.powr and r2.r = r1.r for xml path('')),1,1,'')+']' as s
from ROWNUMBERED r1
group by r1.powr,r1.r
)
select -- now aggregate all the inner arrays per power
stuff((select ',' + cast(g2.s as varchar) from GROUPEDSETS g2 where g2.powr = g1.powr for xml path('')),1,1,'')
from GROUPEDSETS g1
group by g1.powr

Shorter to hard-code arrays?

Yes.

Byte me.

edited body
Source Link

ViolaVoila!

Viola!

Voila!

Post Undeleted by youcantryreachingme
corrections to logic - correctly slice the arrays across the card, rather than down. Update fiddle link.
Source Link

T-SQL, (981 970970 1,168 bytes)

Optimised version:Optimised version

 WITH Gg AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM Gg WHERE n+1<=63n+1<61),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM Gg),N AS(SELECT 0 AS n UNION ALL SELECT n+1 FROM N WHERE n+1<=5),P as(SELECT power(2,n)as p FROM N),S as(select distinct p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select distinct p,e* from S where e>pe>=p and e<61),R as(select castp,(row_number()over(partition by p order by cast(e as varcharint)+'))%5 as r,'+STUFF((SELECTe topfrom 29D),H 'as(select k.p,'['+stuff((select','+cast(El.e as varchar) FROMfrom DR El where El.p=Dp=k.p and El.e>Dr=k.r for xml path('')),1,1,'')+']'as s from R k group by k.p,k.r)select andstuff((select','+cast(x.s Eas varchar)from H x where x.e<61p=z.p FORfor XMLxml PATHpath('')),1,1,'') from DH z group by z.p

Try it online!Online demo

Edit: No restriction on order of sets, so removed an order by clause.Try it online!

EditVerbose version - with notes as SQL comments: However, I just now really understood the part about the order of numbers within sets being mandatory - and my code has not accounted for that requirement. Will leave the answer here and update once that issue is solved.

So far, the only "optimisation" I've done is to remove excess spaces and reduce aliases to single characters. There might be room in the logic to reduce script size further.

Explanation is below this verbose version:

WITH gen -- numbers 1 to 60
AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=63num+1<=60
),
BINARIES -- string representations of binaries 000001 through 111111
as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
N -- numbers 0 through 5
AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM N WHERE n+1<=5
),
POWERS -- powers of 2 based on N (2 to the power of zero, through 2 to the power of 5)
as (
SELECT
power(2,n) as powr FROM N
),
SETELEMENTS -- cross apply the six powers of 2 against the binaries
-- returns 2 cols. col 1 = the power of 2 in question.
-- col 2 is calculated as that power of 2 plus the sum of each power of 2 other than the current row's power value, 
-- but only where a given power of 2 is switched "on" in the binary string, 
-- ie. where the first digit in the string represents 32, the second represents 16 and so on. 
-- That is, if the binary is 100100 then the number will be 
-- the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) 
-- but if the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
-- rows are distinct.
as (
select distinct powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS -- purge calculated numbers smaller than the power of 2 or greater than 60
as (
select distinct powr,elt* from SETELEMENTS where elt >>= powr and elt < 61
)--,
,
ROWNUMBERED -- for each power, number the rows repeatedly from 0 through 5, then back to 0 through 5 again, etc
as (
select castpowr, (row_number() over (partition by powr order by cast(elt as varcharint)))%5 +as 'r,' +elt STUFF( from DISTINCTELEMENTS
),
GROUPEDSETS -- for each row number, within each power, aggregate the numbers as a comma-delimited list and wrap in square brackets - the inner arrays
as (SELECT
select topr1.powr, 29'['+stuff((select ',' + cast(S2r2.elt as varchar) FROMfrom DISTINCTELEMENTSROWNUMBERED S2r2 where S2r2.powr = DISTINCTELEMENTSr1.powr and S2.elt > DISTINCTELEMENTSr2.powrr and= S2r1.elt < 61 FORr XMLfor PATHxml path('')), 1, 1, '' )+']' as s
from DISTINCTELEMENTSROWNUMBERED r1
group by r1.powr,r1.r
order)
select by-- now aggregate all the inner arrays per power
stuff((select ',' + cast(g2.s as varchar) from GROUPEDSETS g2 where g2.powr --= optionalg1.powr clausefor xml path('')),1,1,'')
from GROUPEDSETS g1
group by g1.powr

The explanationViola!

  • The CTE named gen produces the numbers from 1 to 63
  • The CTE named BINARIES produces strings that are binary representations of those numbers (000001 through 111111)
  • The CTE named N produces the numbers 0 through 5
  • The CTE named POWERS produces the powers of 2 based on those numbers 0 through 5 (ie 1, 2, 4, 8, 16, 32)
  • The CTE named SETELEMENTS cross applies the set of powers of 2 (POWERS) against the binary representations of the numbers 1 through 63 (BINARIES)
  • At this point we have a row for every combination of every power of 2 (in our set) against every number from 1 to 63.
  • That same CTE SETELEMENTS returns 2 columns - the power in question (which will be the first element in our final set of results - our magic trick cards), and a calculated number, viz:
  • The number is calculated as that power of 2, plus...
  • The sum of each power of 2, other than the current row's power value, but only where a given power of 2 is switched "on" in the binary string, where, the first digit in the string represents 32, the second represents 16 and so on. That is, if, say, the binary is 100100 then the number will be the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) so long as the current row's power is neither 32, nor 4. If the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
  • The CTE DISTINCELEMENTS simply reduces SETELEMENTS to distinct rows, and also filters the rows so that we only include those rows having a set element value greater than the power in question for that row (per the magic trick cards - all numbers on a card are greater than the power of 2 in the top left corner)
  • Finally, our SELECT statement returns our distinct elements grouped by the power of 2 (which gives us our 6 neat sets) and STUFFs all of the element values for each group, into a comma-delimited string. That stuffing limits the included elements to those under 61 (hey, that's one byte less than <=60), limits to 29 results (plus our power in the top-left corner gives us our 30th element) and prepends the result with the power value of that group/row.

Note 1: Some of the logic pertains to rendering square brackets and commas.

Viola!Note 2: Newer versions of SQLServer have more compact approaches to creating comma-delimited lists. (This was created on SQL Server 2016.)

Although I think it's luck thatNote 3: Arrays for a given card are not sorted (which is ok per the elements in each setspec). Numbers within an array are correctly sorted. In this case, each "card" of the question, renders its arrays on a separate row in correct order ;)the results.

T-SQL, (981 970 bytes)

Optimised version:

 WITH G AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM G WHERE n+1<=63),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM G),N AS(SELECT 0 AS n UNION ALL SELECT n+1 FROM N WHERE n+1<=5),P as(SELECT power(2,n)as p FROM N),S as(select p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select distinct p,e from S where e>p)select cast(p as varchar)+','+STUFF((SELECT top 29 ','+cast(E.e as varchar) FROM D E where E.p=D.p and E.e>D.p and E.e<61 FOR XML PATH('')),1,1,'') from D group by p

Try it online!

Edit: No restriction on order of sets, so removed an order by clause.

Edit: However, I just now really understood the part about the order of numbers within sets being mandatory - and my code has not accounted for that requirement. Will leave the answer here and update once that issue is solved.

So far, the only "optimisation" I've done is to remove excess spaces and reduce aliases to single characters. There might be room in the logic to reduce script size further.

Explanation is below this verbose version:

WITH gen AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=63
),
BINARIES as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
N AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM N WHERE n+1<=5
),
POWERS as (
SELECT
power(2,n) as powr FROM N
),
SETELEMENTS as (
select powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS as (
select distinct powr,elt from SETELEMENTS where elt > powr
)
select cast(powr as varchar) + ',' + STUFF( (SELECT top 29 ',' + cast(S2.elt as varchar) FROM DISTINCTELEMENTS S2 where S2.powr = DISTINCTELEMENTS.powr and S2.elt > DISTINCTELEMENTS.powr and S2.elt < 61 FOR XML PATH ('')), 1, 1, '' )  from DISTINCTELEMENTS
group by powr
order by powr -- optional clause

The explanation

  • The CTE named gen produces the numbers from 1 to 63
  • The CTE named BINARIES produces strings that are binary representations of those numbers (000001 through 111111)
  • The CTE named N produces the numbers 0 through 5
  • The CTE named POWERS produces the powers of 2 based on those numbers 0 through 5 (ie 1, 2, 4, 8, 16, 32)
  • The CTE named SETELEMENTS cross applies the set of powers of 2 (POWERS) against the binary representations of the numbers 1 through 63 (BINARIES)
  • At this point we have a row for every combination of every power of 2 (in our set) against every number from 1 to 63.
  • That same CTE SETELEMENTS returns 2 columns - the power in question (which will be the first element in our final set of results - our magic trick cards), and a calculated number, viz:
  • The number is calculated as that power of 2, plus...
  • The sum of each power of 2, other than the current row's power value, but only where a given power of 2 is switched "on" in the binary string, where, the first digit in the string represents 32, the second represents 16 and so on. That is, if, say, the binary is 100100 then the number will be the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) so long as the current row's power is neither 32, nor 4. If the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
  • The CTE DISTINCELEMENTS simply reduces SETELEMENTS to distinct rows, and also filters the rows so that we only include those rows having a set element value greater than the power in question for that row (per the magic trick cards - all numbers on a card are greater than the power of 2 in the top left corner)
  • Finally, our SELECT statement returns our distinct elements grouped by the power of 2 (which gives us our 6 neat sets) and STUFFs all of the element values for each group, into a comma-delimited string. That stuffing limits the included elements to those under 61 (hey, that's one byte less than <=60), limits to 29 results (plus our power in the top-left corner gives us our 30th element) and prepends the result with the power value of that group/row.

Viola!

Although I think it's luck that the elements in each set are in correct order ;)

T-SQL, (981 970 1,168 bytes)

Optimised version

 WITH g AS(SELECT 1 AS n UNION ALL SELECT n+1 FROM g WHERE n+1<61),B as(SELECT cast(cast(n&32 as bit)as CHAR(1))+cast(cast(n&16 as bit)as CHAR(1))+cast(cast(n&8 as bit)as CHAR(1))+cast(cast(n&4 as bit)as CHAR(1))+cast(cast(n&2 as bit)as CHAR(1))+cast(cast(n&1 as bit)as CHAR(1))as b FROM g),N AS(SELECT 0 AS n UNION ALL SELECT n+1 FROM N WHERE n+1<=5),P as(SELECT power(2,n)as p FROM N),S as(select distinct p,p+(substring(b,6,1)*1)*(case when p=1 then 0 else 1 end)+(substring(b,5,1)*2)*(case when p=2 then 0 else 1 end)+(substring(b,4,1)*4)*(case when p=4 then 0 else 1 end)+(substring(b,3,1)*8)*(case when p=8 then 0 else 1 end)+(substring(b,2,1)*16)*(case when p=16 then 0 else 1 end)+(substring(b,1,1)*32)*(case when p=32 then 0 else 1 end)as e from P cross apply B),D as(select * from S where e>=p and e<61),R as(select p,(row_number()over(partition by p order by cast(e as int)))%5 as r,e from D),H as(select k.p,'['+stuff((select','+cast(l.e as varchar)from R l where l.p=k.p and l.r=k.r for xml path('')),1,1,'')+']'as s from R k group by k.p,k.r)select stuff((select','+cast(x.s as varchar)from H x where x.p=z.p for xml path('')),1,1,'')from H z group by z.p

Online demo

Try it online!

Verbose version - with notes as SQL comments

WITH gen -- numbers 1 to 60
AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=60
),
BINARIES -- string representations of binaries 000001 through 111111
as (
SELECT 
    +cast( cast(num & 32 as bit) as CHAR(1))
    +cast( cast(num & 16 as bit)  as CHAR(1))
    +cast( cast(num & 8 as bit)  as CHAR(1))
    +cast( cast(num & 4 as bit)  as CHAR(1))
    +cast( cast(num & 2 as bit)   as CHAR(1))
    +cast(cast(num & 1 as bit)  as CHAR(1)) as binry FROM gen
),
N -- numbers 0 through 5
AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM N WHERE n+1<=5
),
POWERS -- powers of 2 based on N (2 to the power of zero, through 2 to the power of 5)
as (
SELECT
power(2,n) as powr FROM N
),
SETELEMENTS -- cross apply the six powers of 2 against the binaries
-- returns 2 cols. col 1 = the power of 2 in question.
-- col 2 is calculated as that power of 2 plus the sum of each power of 2 other than the current row's power value, 
-- but only where a given power of 2 is switched "on" in the binary string, 
-- ie. where the first digit in the string represents 32, the second represents 16 and so on. 
-- That is, if the binary is 100100 then the number will be 
-- the sum of (32 x 1) + (16 x 0) + (8 x 0) + (4 x 1) + (2 x 0) + (1 x 0) 
-- but if the current row's power is 32 or 4, then just that number (32 or 4) is excluded from the sum.
-- rows are distinct.
as (
select distinct powr,
powr+
 (substring(binry,6,1) * 1) * (case when powr = 1 then 0 else 1 end)
 +(substring(binry,5,1) * 2) * (case when powr = 2 then 0 else 1 end)
 +(substring(binry,4,1) * 4) * (case when powr = 4 then 0 else 1 end)
 +(substring(binry,3,1) * 8) * (case when powr = 8 then 0 else 1 end)
 +(substring(binry,2,1) * 16) * (case when powr = 16 then 0 else 1 end)
 +(substring(binry,1,1) * 32) * (case when powr = 32 then 0 else 1 end) as elt
from POWERS cross apply BINARIES
),
DISTINCTELEMENTS -- purge calculated numbers smaller than the power of 2 or greater than 60
as (
select * from SETELEMENTS where elt >= powr and elt < 61
)--,
,
ROWNUMBERED -- for each power, number the rows repeatedly from 0 through 5, then back to 0 through 5 again, etc
as (
select powr, (row_number() over (partition by powr order by cast(elt as int)))%5 as r, elt  from DISTINCTELEMENTS
),
GROUPEDSETS -- for each row number, within each power, aggregate the numbers as a comma-delimited list and wrap in square brackets - the inner arrays
as (
select r1.powr, '['+stuff((select ',' + cast(r2.elt as varchar) from ROWNUMBERED r2 where r2.powr = r1.powr and r2.r = r1.r for xml path('')),1,1,'')+']' as s
from ROWNUMBERED r1
group by r1.powr,r1.r
)
select -- now aggregate all the inner arrays per power
stuff((select ',' + cast(g2.s as varchar) from GROUPEDSETS g2 where g2.powr = g1.powr for xml path('')),1,1,'')
from GROUPEDSETS g1
group by g1.powr

Viola!

Note 1: Some of the logic pertains to rendering square brackets and commas.

Note 2: Newer versions of SQLServer have more compact approaches to creating comma-delimited lists. (This was created on SQL Server 2016.)

Note 3: Arrays for a given card are not sorted (which is ok per the spec). Numbers within an array are correctly sorted. In this case, each "card" of the question, renders its arrays on a separate row in the results.

Post Deleted by youcantryreachingme
edits
Source Link
Loading
Source Link
Loading