Trying to accomplish without dynamic SQL (sql server)

Question

All,

I'm trying to pull off an insert from one table to another without using dynamic sql. However, the only solutions I'm coming up with at the moment use dynamic sql. It's been tricky to search for any similar scenarios.

Here are the details:

My starting point is the following legacy table:

CREATE TABLE [dbo].[_Combinations](
[AttributeID] [int] NULL,
[Value] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'1')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'2')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Red')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Orange')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Yellow')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Green')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Blue')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Indigo')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'Violet')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'A')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'B')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'C')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'D')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'E')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'F')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'G')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'H')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'I')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'J')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'K')

SELECT * FROM _Combinations

The _Combinations table contains a key for different types of attributes (AttributeID) and the possible values for each attribute (Value).

In this case, there are 3 different attributes with multiple possible values, however there can be many more (up to 10).

The requirement is then to create every possible combination of each value and store it normalized, as there will be other data stored with each possible combination. I need to store both the attribute keys and values that make up each combination, so it's not just a simple cross join to display each combination. The target table for storing each combination of attributes is this:

CREATE TABLE [dbo].[_CombinedAttributes](
[GroupKey] [int] NULL,
[AttributeID] [int] NULL,
[Value] [varchar](50) NULL
) ON [PRIMARY]

So attribute combination records using the above data would look like this in the target table:

GroupKey    AttributeID Value
1               8         A
1               16        1
1               28        Red
2               8         B
2               16        1
2               28        Red

This gives me what I need. Each group has an identifier and I can track the attributeIDs and values that make up each group. I'm using two scripts to get from the _Combinations table to the format of the _CombinedAttributes table:

-- SCRIPT #1
SELECT Identity(int) AS RowNumber, * INTO #Test
FROM (
SELECT AttributeID AS Attribute1, Value AS Value1 FROM _Combinations WHERE AttributeID = 8) C1
CROSS JOIN 
(
SELECT AttributeID AS Attribute2, Value AS Value2 FROM _Combinations WHERE AttributeID = 16) C2
CROSS JOIN
(
SELECT AttributeID AS Attribute3, Value AS Value3 FROM _Combinations WHERE AttributeID = 28) C3

-- SCRIPT #2

INSERT INTO _CombinedAttributes
SELECT RowNumber AS GroupKey, Attribute1, Value1 
FROM #Test
UNION ALL
SELECT RowNumber, Attribute2, Value2 
FROM #Test
UNION ALL
SELECT RowNumber, Attribute3, Value3
FROM #Test
ORDER BY RowNumber, Attribute1

The above two scripts work, but obviously there's some drawbacks. Namely I need to know how many attributes I'm dealing with and there's hard coding of IDs, so I can't generate this on the fly. The solution I came up with is I build the strings for Script 1 and Script 2 by looping through the attributes in the the _Combinations table and generate execution strings which is long and messy but I can post if needed. Can anyone see a way to pull off the format for the final insert without dynamic sql?

This routine wouldn't be run very much, but it's going to be run enough that I'd like to not be doing any execute string building and use straight SQL.

Thanks in advance.

UPDATE:

When I use a second dataset, Gordon's code is no longer returning correct results, it's creating groups with only 1 attribute near the end, however on this second dataset I get the correct rowcount with Nathan's routine (row count on final result should be 396). But as I stated on the comments, if I use the first dataset, I get the opposite result, Gordon's returns correctly, but Nathan's code has dups. I'm at a loss. Here is the second data set:

DROP TABLE [dbo].[_Combinations] GO

CREATE TABLE [dbo].[_Combinations]( [AttributeID] [int] NULL, [Value] varchar NULL ) ON [PRIMARY] GO

INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'1')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (16, N'2')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'<=39')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'40-44')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'45-49')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'50-54')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'55-64')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (28, N'65+')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'AA')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'JJ')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'CC')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'DD')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'EE')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'KK')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'BB')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'FF')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'GG')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'HH')
INSERT [dbo].[_Combinations] ([AttributeID], [Value]) VALUES (8, N'II')

Answer 1

I think this solves your problem.

Here is the approach. First, observe that the final data has the product of the number of each attribute -- 2*7*11 = 154 rows. Then observe that each value occurs a fixed number of times. For AttributeId = 16, each value occurs 154 / 2, because there are two values.

So, the idea is to calculate the number of times that each value appears. Then, generate the list of all the values. The final challenge is to assign the group numbers to these. For this, I use row_number() partitioned by the attribute id. To be honest, I'm not 100% that the grouping assignment is correct (it makes sense and it passed the eyeball test), but I'm worried that I'm missing a subtlety.

Here is the query:

with attributecount1 as (
      select c.AttributeId, count(*) as cnt
      from _Combinations c
      group by c.AttributeId
     ),
     const as (
      select exp(sum(log(cnt))) as tot, count(*) as numattr
      from attributecount1
     ),
     attributecount as (
       select a.*,
              (tot / a.cnt) as numtimes
       from attributecount1 a cross join const
     ),
     thevalues as (
      select c.AttributeId, c.Value, ac.numtimes, 1 as seqnum
      from AttributeCount ac join
           _Combinations c
           on ac.AttributeId = c.AttributeId
      union all
      select v.AttributeId, v.Value, v.numtimes, v.seqnum + 1
      from thevalues v
      where v.seqnum + 1 <= v.numtimes
     )
select row_number() over (partition by AttributeId order by seqnum, Value) as groupnum,
      *
from thevalues
order by 1, 2

The SQL Fiddle is here.

EDIT:

Unfortunately, I don't have access to SQL Server today and SQL Fiddle is acting up.

The problem is solvable. The above solution works, but -- as stated in my comment -- only when the dimensions are pairwise mutually prime. The problem is the assignment of the group number to the values. It turns out that this is a problem in number theory.

Essentially, we want to enumerate the combinations. If there were 2 in two groups, then it would be:

group 0:  1    1
group 1:  1    2
group 2:  2    1
group 3:  2    2

You can see a relationship between the group number and which values are assigned -- based on the binary representation of the group number. If this were 2x3, then it would look like:

group 0:  1    1
group 1:  1    2
group 2:  1    3
group 3:  2    1
group 4:  2    2
group 5:  2    3

Same idea, but now there is not "binary" representation. Each position in the number would have a different base. No problem.

So, the challenge is mapping a number (such as the group number) to each digit. This requires appropriate division and modulo arithmetic.

The following implements this in Postgres:

with c as (
      select 1 as attrid, '1' as val union all
      select 1 as attrid, '2' as val union all
      select 2 as attrid, 'A' as val union all
      select 2 as attrid, 'B' as val union all
      select 3 as attrid, '10' as val union all
      select 3 as attrid, '20' as val 
     ),
     c1 as (
       select c.*, dense_rank() over (order by attrid) as attrnum,
              dense_rank() over (partition by attrid order by val) as valnum,
              count(*) over (partition by attrid) as cnt
       from c
     ),
     a1 as (
       select attrid, count(*) as cnt,
              cast(round(exp(sum(ln(count(*))) over (order by attrid rows between unbounded preceding and current row))) as int)/count(*) as cum
       from c
       group by attrid
     ),
     a2 as (
       select a.*,
              (select cast(round(exp(sum(ln(cnt)))) as int)
               from a1
               where a1.attrid <= a.attrid
              ) / cnt as cum
       from a1 a
     ),
     const as (
       select cast(round(exp(sum(ln(cnt)))) as int) as numrows
       from a1
     ),
     nums as (
       select 1 as n union all select 2 union all select 3 union all select 4 union all
       select 5 union all select 6 union all select 7 union all select 8
       from const
     ),
     ac as (
      select c1.*, a1.cum, const.numrows
      from c1 join
           a1 on c1.attrid = a1.attrid cross join
           const
     )
select *
from nums join
     ac
     on (nums.n/cum) % cnt = valnum - 1
order by 1, 2;

(Note: generate_series() was not working correctly for some reason with certain joins, which is why it manually generates the sequence of numbers.)

When SQL Fiddle gets working again, I should be able to translate this back to SQL Server.

EDIT II:

Here is the version that works in SQL Server:

with attributecount1 as (
      select c.AttributeId, count(*) as cnt
      from _Combinations c
      group by c.AttributeId
     ),
     const as (
      select cast(round(exp(sum(log(cnt))), 1) as int) as tot, count(*) as numattr
      from attributecount1
     ),
     attributecount as (
       select a.*,
              (tot / a.cnt) as numtimes,
              (select cast(round(exp(sum(log(ac1.cnt))), 1) as int)
               from attributecount1 ac1
               where ac1.AttributeId <= a.AttributeId
              ) / a.cnt as cum
       from attributecount1 a cross join const
     ),
     c as (
       select c.*, ac.numtimes, ac.cum, ac.cnt,
              dense_rank() over (order by c.AttributeId) as attrnum,
              dense_rank() over (partition by c.AttributeId order by Value) as valnum
       from _Combinations c join
            AttributeCount ac
            on ac.AttributeId = c.AttributeId
     ),
     nums as (
       select 1 as n union all
       select 1 + n
       from nums cross join const
       where 1 + n <= const.tot
     )
select *
from nums join
     c
     on (nums.n / c.cum)%c.cnt = c.valnum - 1
option (MAXRECURSION 1000)

THe SQL Fiddle is here.