0

I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios.

Input looks something link this.

Where Each patient has Primary Dx code, Admit Dx code and Additional Dx code.

Note- Additional Dx code sometimes ends with ';' , Each Dx code is seperated by ';'.

There will be only one Primary Dx and Admit Dx column.

enter image description here

My output should be something link this

enter image description here

  • Which database server?? The answer probably depends on which. I'm removing 'mysql' since you seem happy with a sql-server-based answer. – Rick James Apr 22 at 18:16
4

Use a split string table valued function and CROSS APPLY.

--sample split string TVF
create FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

--demo setup
Declare @Table1 table (HIC_Number varchar(5), DateFrom date, DateTo date, PrimaryDx varchar(5), AdmitDX varchar(5), AdditionalDx varchar(100))
insert into @Table1 (HIC_Number,DateFrom,DateTo,PrimaryDx,AdmitDX,AdditionalDx) values
('A','1/1/2018','1/25/2018','E559','E459',';E559;E099;E1121;E784;E039;J449')

--the solution
SELECT HIC_Number
    ,DateFrom
    ,DateTo
    ,PrimaryDx
    ,AdmitDX
    ,Item
FROM @table1 a
CROSS APPLY dbo.SplitString(a.AdditionalDx, ';')
WHERE item <> ' '

| HIC_Number | DateFrom   | DateTo     | PrimaryDx | AdmitDX | Item  |
|------------|------------|------------|-----------|---------|-------|
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E559  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E099  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E1121 |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E784  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | E039  |
| A          | 2018-01-01 | 2018-01-25 | E559      | E459    | J449  |
  • Perfect, Answer and Thank you so much – Avinash Kumar Apr 9 at 20:14
  • 2
    @AvinashKumar, Please mark it as the correct answer if it solved the problem – Biju jose Apr 11 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.