- original data
; WITH TEMP AS
(
SELECT 'WES4300719103000582 SERVICE_SHEET_NO,' WI - C300 MODEL_CODE, SERIAL_NO '8020410', 'WI - C310 MOBILE_MODEL_CODE', '0017876' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200019103001546 SERVICE_SHEET_NO,' WI - C300 MODEL_CODE, SERIAL_NO '8008351', 'WI - C310 MOBILE_MODEL_CODE', '0016674' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200019103001550 SERVICE_SHEET_NO,' WH - CH500 MODEL_CODE, SERIAL_NO '8017811', 'WH - CH500 MOBILE_MODEL_CODE', '8018930' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES1200719110700081 SERVICE_SHEET_NO,' NW - WS413 MODEL_CODE, SERIAL_NO '6073313', 'NW - WS413 MOBILE_MODEL_CODE', '6117484' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES3301119091500140 SERVICE_SHEET_NO,' WI - C600N MODEL_CODE, SERIAL_NO '5061351', 'WI - C600N MOBILE_MODEL_CODE', '5080268' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES4200419091600102 SERVICE_SHEET_NO,' WI - 1000 - x 'MODEL_CODE, SERIAL_NO' 5383857 ', 'WI - 1000 - x' MOBILE_MODEL_CODE, '5463800' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES4200419091600102 SERVICE_SHEET_NO,' WI - 1000 - x 'MODEL_CODE,' 5463800 'SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900283 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5421453', 'NW - WS623 MOBILE_MODEL_CODE', '5601874' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900284 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5601874', 'NW - WS623 MOBILE_MODEL_CODE', '5601875' MOBILE_SERIAL_NO UNION ALL
SELECT 'WES2201219101900285 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5601875', 'NW - WS623 MOBILE_MODEL_CODE', '5601876' MOBILE_SERIAL_NO UNION ALL
SELECT the 'TEST' SERVICE_SHEET_NO, 'TEST - WS623 MODEL_CODE,' TEST_5421453 SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO
B), the AS
(
SELECT SERVICE_SHEET_NO MODEL_CODE, SERIAL_NO, MOBILE_MODEL_CODE MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
- WHERE arjun ODEL_CODE='NW - WS623 AND A.S ERIAL_NO=' 5421453 '
UNION ALL
The SELECT A.S ERVICE_SHEET_NO, arjun ODEL_CODE, A.S ERIAL_NO, arjun OBILE_MODEL_CODE, arjun OBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON arjun ODEL_CODE=B.M OBILE_MODEL_CODE AND A.S ERIAL_NO=B.M OBILE_SERIAL_NO
WHERE arjun OBILE_MODEL_CODE IS NOT NULL AND arjun OBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP
- want
; WITH TEMP AS
(
SELECT 'WES4300719103000582 SERVICE_SHEET_NO,' WI - C300 MODEL_CODE, SERIAL_NO '8020410', 'WI - C310 MOBILE_MODEL_CODE,' 0017876 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES1200019103001546 SERVICE_SHEET_NO,' WI - C300 MODEL_CODE, SERIAL_NO '8008351', 'WI - C310 MOBILE_MODEL_CODE,' 0016674 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES1200019103001550 SERVICE_SHEET_NO,' WH - CH500 MODEL_CODE, SERIAL_NO '8017811', 'WH - CH500 MOBILE_MODEL_CODE,' 8018930 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES1200719110700081 SERVICE_SHEET_NO,' NW - WS413 MODEL_CODE, SERIAL_NO '6073313', 'NW - WS413 MOBILE_MODEL_CODE,' 6117484 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES3301119091500140 SERVICE_SHEET_NO,' WI - C600N MODEL_CODE, SERIAL_NO '5061351', 'WI - C600N MOBILE_MODEL_CODE,' 5080268 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES4200419091600102 SERVICE_SHEET_NO,' WI - 1000 - x 'MODEL_CODE, SERIAL_NO' 5383857 ', 'WI - 1000 - x' MOBILE_MODEL_CODE, '5463800' MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'WES4200419091600102 SERVICE_SHEET_NO,' WI - 1000 - x 'MODEL_CODE,' 5463800 'SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO, 0 (COUNT) UNION ALL
SELECT 'WES2201219101900283 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5421453', 'NW - WS623 MOBILE_MODEL_CODE,' 5601874 'MOBILE_SERIAL_NO, 3 [COUNT] UNION ALL
SELECT 'WES2201219101900284 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5601874', 'NW - WS623 MOBILE_MODEL_CODE,' 5601875 'MOBILE_SERIAL_NO, 2 [COUNT] UNION ALL
SELECT 'WES2201219101900285 SERVICE_SHEET_NO,' NW - WS623 MODEL_CODE, SERIAL_NO '5601875', 'NW - WS623 MOBILE_MODEL_CODE,' 5601876 'MOBILE_SERIAL_NO, 1 [COUNT] UNION ALL
SELECT 'TEST' SERVICE_SHEET_NO, 'TEST - WS623 MODEL_CODE,' TEST_5421453 SERIAL_NO, NULL MOBILE_MODEL_CODE, NULL MOBILE_SERIAL_NO, 0 (COUNT)
B), the AS
(
SELECT SERVICE_SHEET_NO MODEL_CODE, SERIAL_NO, MOBILE_MODEL_CODE MOBILE_SERIAL_NO FROM TEMP A
WHERE MOBILE_MODEL_CODE IS NOT NULL AND MOBILE_SERIAL_NO IS NOT NULL
- WHERE arjun ODEL_CODE='NW - WS623 AND A.S ERIAL_NO=' 5421453 '
UNION ALL
The SELECT A.S ERVICE_SHEET_NO, arjun ODEL_CODE, A.S ERIAL_NO, arjun OBILE_MODEL_CODE, arjun OBILE_SERIAL_NO FROM TEMP A
INNER JOIN TEMP B ON arjun ODEL_CODE=B.M OBILE_MODEL_CODE AND A.S ERIAL_NO=B.M OBILE_SERIAL_NO
WHERE arjun OBILE_MODEL_CODE IS NOT NULL AND arjun OBILE_SERIAL_NO IS NOT NULL
)
SELECT * FROM TEMP
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
