Sql-server – Conversion of Row into Column

sql serversql-server-2008sql-server-2008-r2sql-server-2012

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

Best Answer

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  |
Related Question