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

there exists a function called `STRING_SPLIT` for version 2016+ if it's yours. — Barbaros Özhan, Aug 23 '19 at 17:06
@BarbarosÖzhan i tried it. you would need to re-construct the source table field by field, then join. the actual table has over 30 fields... i was hoping for a more clever way of doing this — Charles Okwuagwu, Aug 23 '19 at 17:08
@CharlesOkwuagwu, most clever way is to store details in a separate table. If refactoring of the db is not possible, use `[DelimitedSplit8K_LEAD]` sp which contrary to `STRING_SPLIT` returns tokens numbered, so you can easily match tokens from two (30) columns by the token number. — Serg, Aug 23 '19 at 17:19
Ideally though, you should be fixing your design, by normalising it and not storing multiple values in a single value. — Thom A, Aug 23 '19 at 17:37
Tedious solutions are often the price you pay for bad database design. — Tab Alleman, Aug 23 '19 at 17:39
Not my design ... data was loaded from a MV platform, i'm just trying to move parts of it to SQL Server — Charles Okwuagwu, Aug 23 '19 at 17:41
@Larnu you see the extract i included from the ETL, we inherit/ have to manage existing systems, not always pretty — Charles Okwuagwu, Aug 23 '19 at 17:42

John Cappelletti · Answer 1 · 2019-08-23T18:47:05.187

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

score 2 · Accepted Answer · answered Aug 23 '19 at 19:21

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"

Thanks, i eventually used a variant of this -- it was the simplest solution — Charles Okwuagwu, Aug 23 '19 at 19:29

score 1 · Answer 3 · answered Aug 23 '19 at 17:49

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

score 1 · Answer 4 · answered Aug 23 '19 at 18:23

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+'%'

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

4 Answers4