Create New row data based column value SQL-CodePudding

I am trying to create a new of data with the existing data but using a separated value in the column. i am running this on sql server 2014 vesrion. Here is my table

create table #test ( ID INT , 
 CODE VARCHAR(10) ,M_U VARCHAR(5) , M_C VARCHAR(5) ,SEPERATOR_A VARCHAR(5)
,CODE1 VARCHAR(10),  M1_U VARCHAR(5) , M1_C VARCHAR(5)  ,SEPERATOR_B VARCHAR(5)
,CODE2 VARCHAR(10),  M2_U VARCHAR(5) , M2_C VARCHAR(5) ) 

INSERT INTO #TEST 
VALUES (  123 , '1' ,'ABC' , 'XYZ', NULL, NULL , NULL ,NULL ,NULL, NULL ,NULL,NULL)
,(  456 , 'G4762' ,'D01' , '398', '^', 'G4765', 'E043' ,NULL ,NULL, NULL ,NULL,NULL)
,(  893 , 'H7832' ,'U73' , 'R12', '^', 'H9833', NULL ,'F82' ,'^', 'H563' ,'U83',NULL)

This is the result i am expecting to have

Any help will be appreciated!!

CodePudding user response：

It is quite old your version, but hos runs on every sql server version

You select for every level a seperate select and UNION ALL them, because it is faster as UNION alone

the rest s ordering it, as you need it

Still saving the data in the fashion of wanted result would make the hole procedure unnecessary and less time consuming

 SELECT 
 ID, CODE, M_U,  M_C , 1 as 'LINE' FROM #test WHERE COALESCE(CODE,M_U, M_C) IS NOT NULL
UNION ALL
 SELECT 
 ID, CODE1, M1_U,  M1_C , 2 as 'LINE' FROM #test WHERE COALESCE(CODE1,M1_U,M1_C) IS NOT NULL
 UNION ALL
 SELECT 
 ID, CODE2, M2_U,  M2_C , 3 as 'LINE' FROM #test WHERE COALESCE(CODE2, M2_U, M2_C) IS NOT NULL
 ORDER BY ID,Line

 ID | CODE  | M_U  | M_C  | LINE
--: | :---- | :--- | :--- | ---:
123 | 1     | ABC  | XYZ  |    1
456 | G4762 | D01  | 398  |    1
456 | G4765 | E043 | null |    2
893 | H7832 | U73  | R12  |    1
893 | H9833 | null | F82  |    2
893 | H563  | U83  | null |    3

db<>fiddle here

CodePudding user response：

You don't need to scan the table three times, as @nbk suggested.

Instead, you can CROSS APPLY a union of the various columns while referring to the outer reference of the table.

SELECT 
  t.ID,
  v.*
FROM #test t
CROSS APPLY (
    SELECT t.CODE, t.M_U, t.M_C, 1
    WHERE t.CODE IS NOT NULL
    UNION ALL
    SELECT t.CODE1, t.M1_U, t.M1_C, 2
    WHERE t.CODE1 IS NOT NULL
    UNION ALL
    SELECT t.CODE2, t.M2_U, t.M2_C, 3
    WHERE t.CODE2 IS NOT NULL
) v(CODE, M_U, M_C, Line)
ORDER BY
  t.ID,
  v.Line;

db<>fiddle, note that my version only has a single table scan.