Query that can scale for x amount of options & values combinations

Question

Trying to return the result of each combination of option values for each automobile, but having some trouble understanding how to do it.

Here is a structure of the tables involved and the desired result. Only thing I've found online is about using cross join, but in this case the number of options dimension is unknown, so that wouldn't quite work out like i had expected.

See the structure/result desired below:

I am familiar with using STUFF() FOR XML, so combining them is not a problem, but the problem is finding the query that it will return the values properly to define.

Your data is somewhat confusing to me. It isn't clear what the logic is for generating the output. — Tim Biegeleisen, Feb 02 '18 at 06:26
You mention that the number of traits (eg COLOR, YEAR) is arbitrary. This means you want an **n-dimensional cartesian product** of the traits for each marque. That makes this question a dupe of [this](https://stackoverflow.com/questions/4757739/how-to-generate-all-possible-data-combinations-in-sql/4757791#4757791) and (in a more modern style) the one I'm about to vote-to-close-as-dupe-of... — AakashM, Feb 02 '18 at 11:05
Possible duplicate of [Cross join N sets of rows in same table](https://stackoverflow.com/questions/12599977/cross-join-n-sets-of-rows-in-same-table) — AakashM, Feb 02 '18 at 11:05
Problem is, the answer at that link suggests the names are already known. In this case they can be dynamic/unknown. — bill, Feb 02 '18 at 15:17

score 0 · Answer 1 · answered Feb 02 '18 at 06:41

0

Check this query

with myTable as (
    select
        *
    from
        (values
            ('BMW','COLOR','Black')
            ,('BMW','COLOR','Red')
            ,('BMW','COLOR','Blue')
            ,('BMW','YEAR','2010')
            ,('BMW','YEAR','2011')
            ,('Ferrari','COLOR','Yellow')
            ,('Ferrari','COLOR','White')
            ,('Ferrari','YEAR','2012')
            ,('Ferrari','YEAR','2013')
        ) t(car, name, value)
)

select
    a.car, a.value + ' ' + b.value
from
    myTable a
    join myTable b on a.car = b.car and a.name <> b.name
where
    a.name = 'COLOR'
    and b.name = 'YEAR'

answered Feb 02 '18 at 06:41

uzi

4,118
1
15
22

What if the number of dimensions are unknown? So the query should return for any number of dimensions.. COLOR, YEAR, MILES, TOP SPEED, etc. - unknown.. – bill Feb 02 '18 at 06:45
If number of dimesions is not known, you will not know how many table joins you need. So, I think in that case you need dynamic SQL – uzi Feb 02 '18 at 06:52
right, but is there a way to avoid dynamic sql in this case? – bill Feb 02 '18 at 06:54
Fornow i do not think that is possible. I will let you know if i will find such a way – uzi Feb 02 '18 at 07:02

score 0 · Answer 2 · answered Feb 02 '18 at 07:28

please try this.

Declare @CAR AS TABLE 
    (
     car varchar(100),
     name varchar(100),
     value varchar(100)
    )

    insert into @CAR 
     values
                ('BMW','COLOR','Black')
                ,('BMW','COLOR','Red')
                ,('BMW','COLOR','Blue')
                ,('BMW','YEAR','2010')
                ,('BMW','YEAR','2011')
                ,('Ferrari','COLOR','Yellow')
                ,('Ferrari','COLOR','White')
                ,('Ferrari','YEAR','2012')
                ,('Ferrari','YEAR','2013')

    Select b.car,a.value + ' ' + b.value as value from @CAR b RIGHT OUTER JOIN (
    Select car,name,value as value from @CAR where name='COLOR') as a on a.car = b.car and b.name='YEAR'

Abdul Rasheed · Answer 3 · 2018-02-02T09:37:35.337

try the below query, Used CTE here. I just used first 3 or 4 CTEs to generate master data, you can change it as per your table structure.

WITH AUTOMOBILES
AS  (
    SELECT 1 ID,'BMW M3' Name
    UNION
    SELECT 2,'Ferrari F 430'
), OPTIONS
AS  (
    SELECT 1 ID,'Color' Name
    UNION
    SELECT 2,'Year'
    UNION
    SELECT 3,'Top Speed'
),AM_VALUES
AS  (
    SELECT 1 ID,1 AutomobileID,1 OptionID,'Black' Value
    union
    select 2,1,1,'Red'
    union
    select 3,1,1,'Blue'
    union
    select 4,1,2,'2010'
    union
    select 5,1,2,'2011'
    union
    select 6,2,1,'Yellow'
    union
    select 7,2,1,'white'
    union
    select 8,2,2,'2012'
    union
    select 9,2,2,'2013'
    union
    select 10,2,3,'Sp210'
    union
    select 11,2,3,'Sp190'
),INPUT_TABLE
AS  (
SELECT  V.ID,V.AutomobileID,A.Name Car,V.OptionID,O.Name, V.Value
        ,DENSE_RANK() OVER(PARTITION BY V.AutomobileID ORDER BY  V.OptionID ) AS OptionRowNo
FROM    AM_VALUES   AS  V
    INNER JOIN AUTOMOBILES  AS  A   ON  A.ID    =   V.AutomobileID
    INNER JOIN OPTIONS      AS  O   ON  O.ID    =   V.OptionID
),VTREE
AS  (
    SELECT  DISTINCT AutomobileID,ID AS Node,NULL AS Parent,OptionRowNo,CAST(Value AS nvarchar) AS Value,1 RowNo
    FROM    INPUT_TABLE
    WHERE   OptionRowNo     =   1
    UNION ALL

    SELECT  V.AutomobileID,I.ID,V.Node,I.OptionRowNo,CAST(V.Value+' '+I.Value AS nvarchar),RowNo=RowNo+1
    FROM    VTREE   AS  V
        INNER JOIN  INPUT_TABLE AS  I   ON  I.AutomobileID  =   V.AutomobileID
                                        AND I.OptionRowNo   >   V.OptionRowNo
)

SELECT  AutomobileID,Value
FROM    VTREE AS V
WHERE   RowNo =     (   SELECT TOP 1 COUNT(DISTINCT I1.OptionID) 
                        FROM INPUT_TABLE AS I1 
                        WHERE I1.AutomobileID = V.AutomobileID
                        GROUP BY I1.AutomobileID)
ORDER BY AutomobileID,Value

Output:-

AutomobileID    Value
1               Black 2010
1               Black 2011
1               Blue 2010
1               Blue 2011
1               Red 2010
1               Red 2011
2               white 2012 Sp190
2               white 2012 Sp210
2               white 2013 Sp190
2               white 2013 Sp210
2               Yellow 2012 Sp190
2               Yellow 2012 Sp210
2               Yellow 2013 Sp190
2               Yellow 2013 Sp210

Query that can scale for x amount of options & values combinations

3 Answers3