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

Question

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

Answer 1

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"

Answer 2

Here is an option that will dynamically unpivot your data (without actually using dynamic SQL), and then pivot the results.

You would just have to list the 30 columns in the for Item in (...) portion

Cross Apply B will convert the ROW into XML

Cross Apply C will UNPIVOT the XML

Cross Apply D will parse/split the delimited string (with a sequence) from C

Then it becomes a small matter for the PIVOT

Example

Declare @YourTable Table ([ID] varchar(50),[DAY] varchar(50),[PRICE] varchar(50))
Insert Into @YourTable Values 
 (1,'01;02;03;04','100;230;110;34.5')
,(2,'01;02;03;04','120;240;510;34.5')

Select *
 From  (
        Select A.ID
              ,C.Item
              ,D.*
         From  @YOurTable A
         Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
         Cross Apply (
                        Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                              ,Value = xAttr.value('.','varchar(max)')
                         From  XMLData.nodes('//@*') xNode(xAttr)
                         Where xAttr.value('local-name(.)','varchar(100)') not in ('Id','Other-Columns','To-Exclude')
                     ) C
         Cross Apply (
                        Select RetSeq = row_number() over (order by (Select null))
                              ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(100)')))
                        From  ( values (cast('<x>' + replace(C.Value,';','</x><x>')+'</x>' as xml))) as A(x)
                        Cross Apply x.nodes('x') AS B(i)
                     ) D
        ) src
 Pivot (max(RetVal) for Item in ([Day],[Price]) ) pvt
 Where Day='02'

Returns

ID  RetSeq  Day Price
1   2       02  230
2   2       02  240

Answer 3

If you can create an additional FUNCTION in your database, you can use this following script to get your desired output.

Create Function:

CREATE FUNCTION FIND_CHARINDEX
(@TargetStr   VARCHAR(8000), 
 @SearchedStr VARCHAR(8000), 
 @Occurrence  INT
)
RETURNS INT
AS
     BEGIN
         DECLARE @pos INT, @counter INT, @ret INT;
         SET @pos = CHARINDEX(@TargetStr, @SearchedStr);
         SET @counter = 1;
         IF @Occurrence = 1
             SET @ret = @pos;
             ELSE
             BEGIN
                 WHILE(@counter < @Occurrence)
                     BEGIN
                         SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1);
                         SET @counter = @counter + 1;
                         SET @pos = @ret;
         END;
         END;
         RETURN(@ret);
     END;

SELECT Statement:

DECLARE @S_String VARCHAR(20) = '05'
DECLARE @S_String_New VARCHAR(20) = ';'+@S_String+';'

SELECT 
REVERSE(
    SUBSTRING(
        REVERSE(
            SUBSTRING(
                ';'+Day+';',
                0, 
                (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String)) +1
            )
        ),
        0,
        CHARINDEX(
            ';',
            REVERSE(SUBSTRING(';'+Day+';',0, (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String)) +1)),
            0
        )
    )
),

REVERSE(
    SUBSTRING(
        REVERSE(
            SUBSTRING(
                ';'+PRICE+';',
                0,    
                (
                    dbo.FIND_CHARINDEX(
                        ';',
                        ';'+PRICE+';',
                        (
                            LEN(SUBSTRING(';'+Day+';',0, (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String)) +1)) 
                                - LEN(REPLACE(SUBSTRING(';'+Day+';',0,  (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String))  +1),';',''))+1
                        )
                    )
                )
            )
        ),
        0,
        CHARINDEX(
            ';',
            REVERSE(
                SUBSTRING(
                    ';'+PRICE+';',
                    0,    
                    (
                        dbo.FIND_CHARINDEX(
                            ';',
                            ';'+PRICE+';',   
                            (
                                LEN(SUBSTRING(';'+Day+';',0, (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String)) +1)) 
                                    - LEN(REPLACE(SUBSTRING(';'+Day+';',0,  (CHARINDEX(@S_String_New,';'+Day+';',0)+LEN(@S_String))  +1),';',''))+1
                            )
                        )
                    )
                )
            ),
            1
        )
    )
)
FROM your_table
WHERE ';'+Day+';' LIKE '%'+@S_String_New+'%'

Answer 4

You can find the original DelimitedSplit8k_LEAD function code at https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2

Demo for 2 columns

select id, details.[day], details.price
from (
     values
     (1,'01;02;03;04','100;230;110;34.5')
    ,(2,'01;02;03;04','120;240;510;34.5')
) t (ID,[Day],Price)
cross apply (
    select d.item [day], p.item price
    from DelimitedSplit8k_LEAD([Day],';') d
    join DelimitedSplit8k_LEAD(Price,';') p on d.ItemNumber = p.ItemNumber
) details