For example I have the below column. The column consists of two types of codes. The codes that include a letter are called HCPC codes and the numeric codes are CPT codes. I need to separate the two types but having trouble finding a way to do so. Here is the original column:

I would essentially need the output to be the following:

Thanks in advance

CodePudding user response：

Since you don't have the same number of codes of each type, the below should work if you only care about that one column:

SELECT
  c1.ProcedureCode CPT, c2.ProcedureCode HCPC
FROM
  (
    SELECT
          mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
    FROM  dbo.myTable AS mt
    WHERE mt.ProcedureCode NOT LIKE '%[A-Za-z]%'
  )   c1
  FULL OUTER JOIN
  (
  SELECT
        mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
  FROM  dbo.myTable AS mt
  WHERE mt.ProcedureCode LIKE '%[A-Za-z]%'
  ) c2 ON c2.rowNo=c1.rowNo;

If you have other columns in the table that you need to preserve, something more like this:

SELECT <other columns>, 
    CASE WHEN ProcedureCode LIKE '%[A-Za-z]%' THEN ProcedureCode ELSE NULL END HCPC, 
    CASE WHEN ProcedureCode NOT LIKE '%[A-Za-z]%' THEN ProcedureCode ELSE NULL END CPT
FROM dbo.myTable

which will preserve other columns, with every row either having a value for HCPC, or a value for CPT, but never both.

CodePudding user response：

Since you said they don't necessarily match with each other and assuming there is even number of rows:

SELECT
  c1.ProcedureCode CPT, c2.ProcedureCode HCPC
FROM
  (
    SELECT
          mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
    FROM  dbo.myTable AS mt
    WHERE mt.ProcedureCode NOT LIKE '%[A-Za-z]%'
  )   c1
  FULL JOIN
    (
      SELECT
            mt.ProcedureCode, ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) rowNo
      FROM  dbo.myTable AS mt
      WHERE mt.ProcedureCode LIKE '%[A-Za-z]%'
    ) c2 ON c2.rowNo=c1.rowNo;

And here is DBFiddle demo.