My db is on SQL server

I have a situation where I have a column that brings me the phase of my card and a column that brings me the date that this card entered the phase, but they are in the format of an "Array". How can I link the phase name with its entry date via query?

I would like it to be like this:

If anyone has any other suggestions on how to handle this I would appreciate it.

CodePudding user response：

You can use OPENJSON to convert the array rows into new rows by the card_id column, and use TRIM to remove any extraneous brackets and whitespaces.

SELECT d.card_id, a.phase_history, a.firstTimeIn
FROM cards d
CROSS APPLY (
   SELECT 
   TRIM('[ ]' FROM c.[value]) AS phase_history, 
   TRIM('[ ]' FROM s.[value]) AS firstTimeIn
   FROM OPENJSON(CONCAT('["', REPLACE(d.phase_history, ',', '","'), '"]')) c
   LEFT OUTER JOIN OPENJSON(CONCAT('["', REPLACE(d.firstTimeIn, ',', '","'), '"]')) s 
      ON c.[key] = s.[key]
) a 
WHERE a.phase_history IN ('Start', 'Compliance')
AND card_id = 8837

Result Set:

The real solution, however, would be to adjust your database design. Storing multiple values in an array format will cause you a lot of problems in the future. Fixing it now rather than later will prevent future headaches.

See Fiddle.