How to reference columns by name based on comma separated value in another column?

I have a table that looks like this:

PartNumber    Part    ValuesList    x1    x2    x3    x4
123456        Fender  x1            11    10    9     12
123456        Fender  x1,x2         11    10    9     12
123456        Fender  x2,x4         11    10    9     12
123456        Fender  x1,x2,x3,x4   11    10    11    12
123456        Fender  x2,x3,x4      11    10    9     12

For each row, how can I find the lowest value from columns x1, x2, x3, x4 based on the column names given in ValuesList?

For example, row 1 is easy. The ValuesList value is x1, so I only need to look at column x1 and the lowest value is 11.

Row 2 .. the ValuesList values are x1,x2. I need the lower number between columns x1 and x2 (11 and 10). The lowest value is 10.

Row 3 .. the ValuesList values are x2,x4. I need the lower number between colums x2 and x4 (10 and 12). The lowest value is 10.

Row 4 .. the ValuesList values are x1,x2,x3,x4. I need the lower number between columns x1, x2, x3 and x4 (11, 10, 11, 12). The lowest value would be 10.

Row 5 .. the ValuesList values are x2,x3,x4. I need the lower number between columns x2, x3 and x4 (10, 9, 12). The lowest value would be 9.

The actual table has hundreds of thousands of rows.

In pseudocode, I would want something like

SELECT PartNumber, Part, MIN(ColumnValue) WHERE ColumnName IN (SELECT ColumnNames FROM ValuesList)

In the case where the lowest number appears in multiple columns, it doesn't matter which column the lowest number is taken from.

Thanks!

3 answers

  • answered 2021-07-27 17:32 Larnu

    I'm going to assume you are using a recent version of SQL Server here, and thus have access to STRING_SPLIT. If not, then you'll need to use a user defined string splitter; there are plenty out there if you do a search (though I recommend using a set based one). Then, along side STRING_SPLIT you can unpivot your data, I use a table VALUES construct for that, implicitly JOIN in the WHERE and finally aggregate:

    CREATE TABLE dbo.YourTable (PartNumber int,
                                Part varchar(10),
                                ValuesList varchar(8000),
                                x1 int,
                                x2 int,
                                x3 int,
                                x4 int);
    GO
    INSERT INTO dbo.YourTable
    VALUES(123456,'Fender','x1         ',11,10,9 ,12),
          (123456,'Fender','x1,x2      ',11,10,9 ,12),
          (123456,'Fender','x2,x4      ',11,10,9 ,12),
          (123456,'Fender','x1,x2,x3,x4',11,10,11,12),
          (123456,'Fender','x2,x3,x4   ',11,10,9 ,12);
    GO
    
    SELECT YT.PartNumber,
           YT.Part,
           YT.ValuesList,
           MIN(V.XVal) AS MinX
    FROM dbo.YourTable YT
         CROSS APPLY STRING_SPLIT(YT.ValuesList,',') SS
         CROSS APPLY (VALUES(N'x1',x1),
                            (N'x2',x2),
                            (N'x3',x3),
                            (N'x4',x4))V(XCol,XVal)
    WHERE SS.[value] = V.XCol
    GROUP BY YT.PartNumber,
             YT.Part,
             YT.ValuesList;
    GO
    
    DROP TABLE dbo.YourTable;
    

    Of course, I really suggest you normalise your design here. Don't store delimited data, and don't repeat column values. If you normalise your design you'll likely just need a couple of JOINs and an aggregate.

  • answered 2021-07-27 18:01 John Cappelletti

    Just another option

    Example

    Select A.PartNumber
          ,A.Part
          ,A.ValuesList
          ,B.MinValue 
     From  YourTable A
     Cross Apply ( Select MinValue=min(Value)
                    From  (values (case when charindex('x1',ValuesList)>0 then x1 end)
                                 ,(case when charindex('x2',ValuesList)>0 then x2 end)
                                 ,(case when charindex('x3',ValuesList)>0 then x3 end)
                                 ,(case when charindex('x4',ValuesList)>0 then x4 end)
                          ) B1(Value)
                 )B
    

    Results

    PartNumber  Part    ValuesList  MinValue
    123456      Fender  x1          11
    123456      Fender  x1,x2       10
    123456      Fender  x1,x2,x3,x4 10
    123456      Fender  x2,x3,x4    9
    123456      Fender  x2,x4       10
    

  • answered 2021-07-27 18:17 Gordon Linoff

    I would strongly recommend doing this without an outer group by:

    SELECT t.*, s.min_val
    FROM t OUTER APPLY
         (SELECT MIN(val) as min_val
          FROM STRING_SPLIT(YT.ValuesList,',') s JOIN
               (VALUES('x1', x1), ('x2', x2), ('x3', x3),('x4', x4)
               )v(col, val)
               ON s.value = v.name
         ) s;
    

    Using APPLY on values within a row usually has much better performance characteristics than aggregating over an entire data set.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum