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

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 sideSTRING_SPLIT
you can unpivot your data, I use a tableVALUES
construct for that, implicitlyJOIN
in theWHERE
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
JOIN
s and an aggregate. 
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

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.
do you know?
how many words do you know