Separate values which are not delimited

meera · April 26, 2024, 3:20pm

Master table of Diagnosis

Sno	Diagnosis
1	Acute diarrhoea (Gastroenteritis)
2	Acute fever
3	Acute Suppurative Otitis Media (ASOM)
4	Anaemia
5	Aphthous ulcers (Stomatitis)
6	ARTI (URI)
7	Breast abscess
8	Conjunctivitis (Purulent)
9	Conjunctivitis (Xen-purulent)
10	Dysentry
11	Dyspepsia
12	External ear furunculosis
13	Genital ulcer
14	LRTI (Pneumonia)
15	Osteoarthritis
16	Pica syndrome
17	PID
18	Post menopausal bleeding
19	Pulmonary tuberculosis
20	Pyoderma
21	Scabies
22	Tinea capitis
23	Tinea corporis
24	Tinea cruris
25	Tinea versicolor
26	Traumatic wounds
27	Vaginal discharge
28	Worm infestation

Data of diagnosis collected via form

Sno	Diagnosis data
1	ARTI (URI) Aphthous ulcers (Stomatitis)
2	ARTI (URI) Acute diarrhoea (Gastroenteritis)
3	Acute fever Pyoderma
4	Acute diarrhoea (Gastroenteritis) ARTI (URI)
5	ARTI (URI) Acute diarrhoea (Gastroenteritis)
6	Acute diarrhoea (Gastroenteritis) ARTI (URI)
7	Acute diarrhoea (Gastroenteritis) Pyoderma
8	Acute diarrhoea (Gastroenteritis) ARTI (URI)
9	Anaemia Tinea corporis
10	ARTI (URI) Acute diarrhoea (Gastroenteritis)
11	Scabies Acute fever
12	Acute diarrhoea (Gastroenteritis) Worm infestation
13	ARTI (URI) Acute diarrhoea (Gastroenteritis)
14	ARTI (URI) Traumatic wounds
15	Dyspepsia Worm infestation
16	Acute diarrhoea (Gastroenteritis) ARTI (URI)
17	ARTI (URI) Acute diarrhoea (Gastroenteritis)
18	Anaemia Worm infestation
19	Acute diarrhoea (Gastroenteritis) Acute fever

As seen, second table has got more than 1 value from the first table concatenated with 1 space. how to separate the second table value based on the first table values?

Is there a way to have the second table value separated as per first table value? like below,

mike01 · April 26, 2024, 6:26pm

This assumes there will be at most 2 diagnosis on the same line

Create table #Diagnosis (ID int, Diagnosis varchar(100))
Create table #DiagnosisData (ID int, DiagnosisData varchar(200))

insert into #Diagnosis values
(1,'Acute diarrhoea (Gastroenteritis)'),
(2,'Acute fever'),
(3,'Acute Suppurative Otitis Media (ASOM)'),
(4,'Anaemia'),
(5,'Aphthous ulcers (Stomatitis)'),
(6,'ARTI (URI)'),
(7,'Breast abscess'),
(8,'Conjunctivitis (Purulent)'),
(9,'Conjunctivitis (Xen-purulent)'),
(10,'Dysentry'),
(11,'Dyspepsia'),
(12,'External ear furunculosis'),
(13,'Genital ulcer'),
(14,'LRTI (Pneumonia)'),
(15,'Osteoarthritis'),
(16,'Pica syndrome'),
(17,'PID'),
(18,'Post menopausal bleeding'),
(19,'Pulmonary tuberculosis'),
(20,'Pyoderma'),
(21,'Scabies'),
(22,'Tinea capitis'),
(23,'Tinea corporis'),
(24,'Tinea cruris'),
(25,'Tinea versicolor'),
(26,'Traumatic wounds'),
(27,'Vaginal discharge'),
(28,'Worm infestation')

insert into #DiagnosisData values
(1,'ARTI (URI) Aphthous ulcers (Stomatitis)'),
(2,'ARTI (URI) Acute diarrhoea (Gastroenteritis)'),
(3,'Acute fever Pyoderma'),
(4,'Acute diarrhoea (Gastroenteritis) ARTI (URI)'),
(5,'ARTI (URI) Acute diarrhoea (Gastroenteritis)'),
(6,'Acute diarrhoea (Gastroenteritis) ARTI (URI)'),
(7,'Acute diarrhoea (Gastroenteritis) Pyoderma'),
(8,'Acute diarrhoea (Gastroenteritis) ARTI (URI)'),
(9,'Anaemia Tinea corporis'),
(10,'ARTI (URI) Acute diarrhoea (Gastroenteritis)'),
(11,'Scabies Acute fever'),
(12,'Acute diarrhoea (Gastroenteritis) Worm infestation'),
(13,'ARTI (URI) Acute diarrhoea (Gastroenteritis)'),
(14,'ARTI (URI) Traumatic wounds'),
(15,'Dyspepsia Worm infestation'),
(16,'Acute diarrhoea (Gastroenteritis) ARTI (URI)'),
(17,'ARTI (URI) Acute diarrhoea (Gastroenteritis)'),
(18,'Anaemia Worm infestation'),
(19,'Acute diarrhoea (Gastroenteritis) Acute fever'),
(20,'Pyoderma')

;with cte as (
Select dd.id, dd.DiagnosisData, d.Diagnosis, row_number() over (partition by dd.id order by d.diagnosis) as RowN
  from #DiagnosisData  dd
	join #Diagnosis   d
		on charindex(d.Diagnosis,dd.DiagnosisData) > 0)

select c.DiagnosisData, c.Diagnosis, c1.Diagnosis
  from cte c
	left join cte c1
		on c.id = c1.id
		and C1.RowN = 2
where c.RowN = 1

ScottPletcher · April 26, 2024, 6:44pm

This code also assumes no more than 2 diagnoses. If it can be more, let us know.


SELECT ca1.*
FROM #DiagnosisData dd
INNER JOIN #Diagnosis d ON d.Diagnosis = LEFT(dd.DiagnosisData, LEN(d.Diagnosis)) 
CROSS APPLY (
    SELECT dd.ID, d.Diagnosis
    UNION ALL
    SELECT dd.ID, SUBSTRING(dd.DiagnosisData, LEN(d.Diagnosis) + 1, 8000)
    WHERE SUBSTRING(dd.DiagnosisData, LEN(d.Diagnosis) + 1, 8000) <> ''    
) AS ca1
ORDER BY ID

meera · April 27, 2024, 3:18pm

@ScottPletcher
Yes, in future obviously the count can increase.
The master values are available as Multi select options and that is how that application outputs all selected values with single space separation.

Request you to kindly let me know how to achieve right soln in case of more than 2 values / n values selected? - Thank you so much in advance!!

robert_volk · April 27, 2024, 4:26pm

This will accommodate any number of diagnoses:

SELECT dd.*,d.Diagnosis
, ROW_NUMBER() OVER (PARTITION BY dd.ID ORDER BY CHARINDEX(d.Diagnosis,dd.DiagnosisData)) row_num
FROM #DiagnosisData dd 
INNER JOIN #Diagnosis d ON LEN(REPLACE(dd.DiagnosisData,d.Diagnosis,''))<LEN(dd.DiagnosisData)

There's probably a more elegant way to JOIN it, the only real advantage is that it's short.
You can test it with this value:

insert into #DiagnosisData values
(21,'Acute diarrhoea (Gastroenteritis) Acute fever Tinea capitis Pyoderma')

You will see 4 diagnoses for item 21, each is numbered according to its position in the concatenated string.

It's possible for a diagnosis to overlap with another and cause an invalid combination, for example:

Tinnitus
Tinnitus acute

Would generate 3 rows for 'Tinnitus Acute Fever', which would be incorrect. So far your example data is clean and consistent, but having space-delimited combinations that can include spaces in singular elements will always be problematic.

If you need to pivot columns into multiple diagnoses:

SELECT ID,DiagnosisData,Diagnosis_1,Diagnosis_2,Diagnosis_3,Diagnosis_4
FROM(
SELECT dd.*,d.Diagnosis
, CONCAT('Diagnosis_',ROW_NUMBER() OVER (PARTITION BY dd.ID ORDER BY CHARINDEX(d.Diagnosis,dd.DiagnosisData))) row_num
FROM #DiagnosisData dd 
INNER JOIN #Diagnosis d ON LEN(REPLACE(dd.DiagnosisData,d.Diagnosis,''))<LEN(dd.DiagnosisData)) a
PIVOT(MAX(Diagnosis) FOR row_num IN(Diagnosis_1,Diagnosis_2,Diagnosis_3,Diagnosis_4)) b
ORDER BY ID

Note that you'd need to hard-code the maximum number of diagnoses, so better to pivot this in Excel or a reporting tool like SSRS. There's ways to get dynamic number of pivot columns, search on this site or elsewhere, or someone might post a solution here.

meera · April 29, 2024, 3:10pm

Thank you so much @robert_volk !!!