Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ddamico
Yak Posting Veteran
76 Posts |
 Posted - 2010-11-24 : 12:04:01
|
| Good day,I need some assistance working with a query that would return a permutation style result set. My table is as follows.DECLARE @Incoming TABLE( ProductFeatureId int, ProductFeatureGroupId varchar(3))INSERT INTO @Incoming VALUES (1, '100')INSERT INTO @Incoming VALUES (2, '100')INSERT INTO @Incoming VALUES (4, '100')INSERT INTO @Incoming VALUES (8, '200')INSERT INTO @Incoming VALUES (64, '300')INSERT INTO @Incoming VALUES (128, '300')The results should return the combination of all ProductFeatureGroups. 1+8+64 2+8+644+8+641+8+1282+8+1284+8+128The goal is to add the values and store as a bit field so we don't have to worry about the number of columns, The issue is we could have N number of ProductFeatureGroups. I am trying to find an elegant way of doing this without looping. I am thinking recursive CTE I am not quite sure of how to do it.Any help would be appreciated. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddamico
Yak Posting Veteran
76 Posts |
Posted - 2010-11-24 : 12:53:30
|
| We need all combination of ProductFeatureGroup 100,200 and 300 combined. 64 and 128 are members of 300 so they wouldn't be considered together.We would expect that we may get multiple rows with the same values but we can reduce by aggregation later. |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-11-24 : 13:04:02
|
| First of all I do not understand your specifications. Secondly, we do not use assembly bit fields in SQL columns [columns are not fields]. Remember First Normal Form? The Information Principle? These are the foundations of RDBMS. Are you trying to aggregate product features into groups? That relationship would go into another table, with a group id column and a single feature id column that references the features table.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
ddamico
Yak Posting Veteran
76 Posts |
Posted - 2010-11-24 : 13:35:57
|
| Maybe I can clarify a bit with an example. There is a website and there is a sections that has different product groupsA (Engine) B (Doors) C (Color)---------- --------- -----------10 - 4cyl 100 - 2 1000 - red20 - v6 200 - 4 2000 - blue30 - v8The user can check the checkboxes for any of the above criteria (features). We have a table that contains the features1 102 204 308 10016 20032 100064 2000Base on the checkboxes we will know what criteria they have selectedand add the features among the groups Example: they select all engine types + 2 door car + both red and blue.The next thing is to select back the list of product(s) from our database that have all the criteria that add up to the same bit combinations. Since, we don't know the number Feature Groups ahead of timewe would like to have a single table that has a row for each feature group and feature. The trick is to use that table to generate the combinations which would be the feature(a) + feature(b) + .... + feature(x). The issue is that we don't know how many feature groups there and don't want to use dynamic sqlI was able to manage it for a two feature groups select a.feature + b.featurefrom featuregroup a, featuregroup bwhere a.featuregroupid <> b.featuregroupid |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Permutation Type Query