Want to create logic in SQL Server to create a derived column based on comparing two comma-separated columns (for reference refer to the attached screenshot).

image

Scenario: I have two comma-separated columns

• I need to calculate values for only "Closed" values (in column1)
• In column 1st first row- I have a Closed value for A in column 2 so in the output column – it will be A.
• In column 1 2nd row – I don’t have any closed so the output will be Null; in column 1 3rd row - I have a Closed value for E in column 2 so in the output column – it will be E.
• And so on…

The position of Closed value is not fixed. It can be anywhere such as - either at 1st place/position or 2nd place or in 3rd place.

There are 6 million rows in the table.

Question: what will be the best approach/logic in SQL Server? Or what should be the query for retrieving the value?

CodePudding user response：

A minimal reproducible example ##1-4 is not provided.

Shooting from the hip.

Please try the following solution based on XQuery.

XML and XQuery data model is based on ordered sequences, exactly what we need.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col1 VARCHAR(100), col2 VARCHAR(50));
INSERT INTO @tbl (col1, col2) VALUES
('Closed,Open,In-process', 'A,B,C'),
('Open,In-process,New', 'A,B,D'),
('New,Closed', 'B,E'),
('Closed', 'H'),
('Open,New,Closed', 'F,G,N'),
('Closed,Closed,Closed', 'A,B,C');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT * --tbl.*
    , Result = x.value('(/root/r[sql:column("t.pos")]/text())[1]', 'VARCHAR(10)')
FROM @tbl AS tbl
CROSS APPLY (SELECT x = TRY_CAST('<root><r><![CDATA['   
      REPLACE(col1, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)
      .query('
      for $x in /root/r[text()="Closed"][1]
      return count(root/r[. << $x])   1
      ').value('text()[1]','INT')) AS t(pos)
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(col2, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t2(x);

Output

 ---- ------------------------ ------- -------- 
| ID |          col1          | col2  | Result |
 ---- ------------------------ ------- -------- 
|  1 | Closed,Open,In-process | A,B,C | A      |
|  2 | Open,In-process,New    | A,B,D | NULL   |
|  3 | New,Closed             | B,E   | E      |
|  4 | Closed                 | H     | H      |
|  5 | Open,New,Closed        | F,G,N | N      |
|  6 | Closed,Closed,Closed   | A,B,C | A      |
 ---- ------------------------ ------- -------- 

CodePudding user response：

Can I apply Where clause in cross-apply?