CODEWITHSUNDEEP
sqlsql-server
Sanky
Sanky

Reputation: 41

How to separate values (delimiter separated) as different columns from SQL Server table having multiple rows?

I have a SQL Server table which has multiple rows as below. Each row has one delimiter '^'. I want to make a separate column from each row.

Let's say this is the original table:

Sourcetable (only one column)

StringVal
-------------------------------------------------
57^H:\ ^ 200^Test ^2018-09-19 08:20:01.000
8^T:\ ^ 88^Test1 ^2018-09-1 08:00:01.000
33^D:\ ^ 40^Test2 ^2018-10-1 08:10:01.000

My request is to select columns as below in output by using above table

DestinationTable (with 5 columns)

FreeSpace | Total    | Drive | Server | Date
----------+----------+-------+--------+--------------------------
 57       |   200    | H:\   |  Test  | 2018-09-19 08:20:01.000
  8       |    88    | T:\   |  Test1 | 2018-09-1 08:00:01.000
 33       |    40    | D:\   |  Test2 | 2018-10-1 08:10:01.000

Note: String from source table also contains null values. Also that string contains many blank places within it without any order. So that also need to be handled.

I have tried to use string function but its giving me values for only up to the first delimiter and skipping further. 

SELECT
    Substring(string, 1, Charindex('^', string) - 1) as Name,
    Substring(string, 4, Charindex('^', n) + 3) as Name1
FROM
    Sourcetable

I expect output as below with 5 different columns 

FreeSpace | Total    | Drive | Server | Date
----------+----------+-------+--------+--------------------------
 57       |   200    | H:\   |  Test  | 2018-09-19 08:20:01.000
  8       |    88    | T:\   |  Test1 | 2018-09-1 08:00:01.000
 33       |    40    | D:\   |  Test2 | 2018-10-1 08:10:01.000

Upvotes: 1

Views: 1308

Answers (3)

Pugal
Pugal

Reputation: 549

My Input

select * from StringVal

StringVal
57^H:\ ^ 200^Test ^2018-09-19 08:20:01.000
8^T:\ ^ 88^Test1 ^2018-09-1 08:00:01.000
33^D:\ ^ 40^Test2 ^2018-10-1 08:10:01.000

Query

;with cte 
as (
 select 
   CONVERT (varchar (255), StringVal) StringVal
 , convert (varchar (255), StringVal) want -- 'want' means wanted column.
 , ROW_NUMBER () over (partition by StringVal order by (select null)) id  
 -- Row id. Based on original value. for track/count the '^'.
 from StringVal
 union all
 select 
   CONVERT (varchar (255), StringVal) 
 , convert ( varchar (255)
  , stuff (
     want, CHARINDEX ('^', want),1
     ,choose (id, '</FreeSpace ><Drive>','</Drive><Total>', '</Total><Server>','</Server><Date>')
     -- Replace the '^' by XML tags based on column's order.
  )
 ) 
 , id + 1
 from cte
 where want like '%^%'
)
select 
  FreeSpace.value('.', 'varchar (255)') FreeSpace
, Total.value('.', 'varchar (255)') Total
, Drive.value('.', 'varchar (255)') Drive
, Server.value('.', 'varchar (255)') Server
, Date.value('.', 'varchar (255)') Date
from (
 select convert(xml, '<StringVal><FreeSpace>' + want + '</Date></StringVal>') StringVal 
 from cte where id = 5
) xml
cross apply -- I'm not good in XML. so I need lot xml.nodes.
xml.StringVal.nodes('/StringVal/FreeSpace') FreeSpace(FreeSpace)
cross apply 
xml.StringVal.nodes('/StringVal/Total') Total(Total)
cross apply 
xml.StringVal.nodes('/StringVal/Drive') Drive(Drive)
cross apply 
xml.StringVal.nodes('/StringVal/Server') Server(Server)
cross apply 
xml.StringVal.nodes('/StringVal/Date') Date(Date)

Final Output

FreeSpace   Total   Drive   Server  Date
8           88      T:\     Test1   2018-09-1 08:00:01.000
57          200     H:\     Test    2018-09-19 08:20:01.000
33          40      D:\     Test2   2018-10-1 08:10:01.000

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

How about

WITH CTE AS
(
SELECT *
FROM Strings S CROSS APPLY
    (
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN,
             Value
      FROM STRING_SPLIT(Str, '^')
    ) SP
)
SELECT ID,
       Str,
       MAX(CASE WHEN RN = 1 THEN Value END) FreeSpace,
       MAX(CASE WHEN RN = 2 THEN Value END) DriveLetter,
       MAX(CASE WHEN RN = 3 THEN Value END) Total,
       MAX(CASE WHEN RN = 4 THEN Value END) Server,
       MAX(CASE WHEN RN = 5 THEN Value END) [Date]
FROM CTE
GROUP BY ID,
         Str;

Demo

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You should dump your current single column table to a text file, and then reimport using SQL Server's import wizard. The delimiter you want to use is:

\s*^\s*

If the wizard does not accept this delimiter, then you might have to preprocess your file. You may do a regex replacement of \s*^\s* and replace with just comma. Then, import to SQL Server via the wizard, using comma as a separator.

Upvotes: 1

Related Questions