SQL Server Query

Question

Hello,

Anyone can help me regarding SQL query below.

I have record like this

and want to modify so the result is like below and store in table.

For dummy data you can use :

DECLARE @TBL TABLE(MultiValueMessages VARCHAR(100))

INSERT INTO @TBL VALUES

('vd:08/04/2024 08/04/2024 08/04/2024, vn:AA01 BB01 CC01, vr:S1 S2 S3')

INSERT INTO @TBL VALUES

('vd:08/03/2024 08/03/2024 08/03/2024, vn:MM01 NN02 O22, vr:S1 S2 S3')

select * from @TBL

Answer 1

This will work. I made it a bit longer so you could see step by step how I broke out each value. Also note that you had no order in the source table, so I've ordered the output by the message itself. That's all the first CTE does.

You could just change the first ORDER BY if there is any other order.

It could also be done via pivoting methods but I think this would be the most understandable way, as you can just query after each CTE to see what each achieves.

WITH MVRows
AS
(
    SELECT t.MultiValueMessages, ROW_NUMBER() OVER(ORDER BY t.MultiValueMessages) AS RowNumber
    FROM @TBL AS t
),
MVGroups
AS
(
    SELECT mvr.RowNumber,
           LEFT(TRIM(ss.value), 2) AS GroupType, 
           SUBSTRING(TRIM(ss.value), 4,1000) AS GroupValue
    FROM MVRows AS mvr
    CROSS APPLY (SELECT value FROM STRING_SPLIT(mvr.MultiValueMessages, ',')) AS ss
),
MVSplitRows
AS
(
    SELECT mvg.RowNumber, mvg.GroupType, ss.ordinal, ss.value AS ColumnValue
    FROM MVGroups AS mvg
    CROSS APPLY (SELECT value, ordinal FROM STRING_SPLIT(mvg.GroupValue, ' ', 1)) AS ss
)

SELECT (SELECT mvsrl.ColumnValue FROM MVSplitRows AS mvsrl
                                 WHERE mvsrl.GroupType = 'vn'
                                 AND mvsrl.ordinal = mvsr.ordinal
                                 AND mvsrl.RowNumber = mvsr.RowNumber) AS vn,
       (SELECT mvsrl.ColumnValue FROM MVSplitRows AS mvsrl
                                 WHERE mvsrl.GroupType = 'vd'
                                 AND mvsrl.ordinal = mvsr.ordinal
                                 AND mvsrl.RowNumber = mvsr.RowNumber) AS vd,
       'S' + CAST(mvsr.ordinal AS varchar(10)) AS vr     
FROM MVSplitRows AS mvsr
GROUP BY mvsr.RowNumber, mvsr.ordinal
ORDER BY mvsr.RowNumber, mvsr.ordinal;