Search code examples
sqlsql-serversplitmultivalue

HOW TO query multi-valued column by key in SQL SERVER

How to query a multivalued column for a specific key?

Sample Data

ID   DAY              PRICE
1    01;02;03;04;...  100;230;110;34.5;...
2    01;02;03;04;...  120;240;510;34.5;...

For example:

select ... where DAY key = '02'

Expected:

ID DAY PRICE
1  02  230
2  02  240

Notes

The actual table has over 30 fields.

Joining multiple CROSS APPLY SPLIT_STRING looked like a tedious solution

Solution

  • This solution uses cte and I hope it works for you:

    with cte1 as
( 
select id, value daykey,
 row_number() over(order by (select null)) as rowid
from mvct
cross apply string_split(day, ";")
),
cte2 as
(
select id, value pricekey,
 row_number() over(order by (select null)) as rowid
from mvct
cross apply string_split(price, ";")
)
select cte1.id, cte1.daykey, cte2.pricekey
from cte1
inner join cte2 on cte1.id = cte2.id
and cte1.rowid = cte2.rowid
and cte1.daykey = "02"