Get data into different columns from single pipe separated column

Question

I have pipe separated data in one column pf table data something like this

I want to put that data in different column something like this

How to separate pipe separated data into different column is SQL?

I tried doing something like this

;WITH GRNSumm AS (
      SELECT Value
           ,ID = Row_Number() OVER (ORDER BY Value)
      FROM   [Split]('asfasdjfsdg|sdhgs|sdg|sdgl','|')
    )
    SELECT 
    col1 = MAX(CASE WHEN ID = 1 THEN Value ELSE NULL END)
         , col2 = MAX(CASE WHEN ID = 2 THEN Value ELSE NULL END)
         , col3 = MAX(CASE WHEN ID = 3 THEN Value ELSE NULL END)
         , col4 = MAX(CASE WHEN ID = 4 THEN Value ELSE NULL END)
    into #tempdata
    FROM   GRNSumm

But it does not work for me

What does "it does not work for me" mean? – Gordon Linoff Jul 15 '20 at 11:17 — Gordon Linoff, Jul 15 '20 at 11:17

score 5 · Accepted Answer · edited Jul 15 '20 at 09:52

5

if you're using SQL Server 2017 and above you can use the table valued function string_split like this:

select * from table1 outer apply string_split(col3, '|')

The problem now is, that you need to pivot the rows to columns in order to get the pattern of your second table.

Now in your case it could look like this:

;WITH 
input_data as (
    select 1 col1, 1 col2, 'abc|bcd|asd|qwe' col3 union all
    select 1 col1, 2 col2, 'abc|dhejg|dlsjd|qwe' col3 union all
    select 2 col1, 3 col2, 'hdf|sjd|sdjf|sjdgs' col3 union all
    select 2 col1, 4 col2, 'asfasdjfsdg|sdhgs|sdg|sdgl' col3
),
GRNSumm AS (
    SELECT col1, col2, col3, Value, Row_Number() OVER (partition by col1, col2 ORDER BY Value) id
    FROM   input_data outer apply [Split](col3, '|') --put string_split here or your own tvf
)
SELECT col1, col2, col3, [1] as PackingSlip, [2] as ReceivingLog, [3] as DateReceived, [4] as GLExpenseAccount
  from GRNSumm
 PIVOT  
(MAX(Value) FOR id IN ([1], [2], [3], [4])) AS PivotTable;

I say could, because now it gets tricky. The outer apply will not guarantee for any order of the rows depending on the position of the content. Moreover, the row_number over (order by value) will ensure that the order is dependent on the content and not the position of the string.

edited Jul 15 '20 at 09:52

vaibhav shah

4,939
19
58
96

answered Jul 15 '20 at 09:33

casenonsensitive

955
2
9
18

I am not using SQL Server 2017 – vaibhav shah Jul 15 '20 at 09:37
Which version are you using? – casenonsensitive Jul 15 '20 at 09:37
sql server 2014 – vaibhav shah Jul 15 '20 at 09:38
but that does not work for multiple rows as I can not pas multiple data in spli function – vaibhav shah Jul 15 '20 at 09:41
The edit shows how it works with multiple rows for your case. – casenonsensitive Jul 15 '20 at 09:51
If you are using a older version, create your own function: [link](https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) – Claudio Lopes Jul 15 '20 at 10:14

score 1 · Answer 2 · answered Jul 15 '20 at 11:26

Your question suggests that you have a split function. Unfortunately, the built-in split function in SQL does not have a position.

If you don't have duplicate values within a string, then you can use charindex() to identify the position:

WITH input_data as (
      select 1 col1, 1 col2, 'abc|bcd|asd|qwe' col3 union all
      select 1 col1, 2 col2, 'abc|dhejg|dlsjd|qwe' col3 union all
      select 2 col1, 3 col2, 'hdf|sjd|sdjf|sjdgs' col3 union all
      select 2 col1, 4 col2, 'asfasdjfsdg|sdhgs|sdg|sdgl' col3
     )
SELECT id.*, x.*
FROM input_data id CROSS APPLY
     (SELECT MAX(CASE WHEN seqnum = 1 THEN value END) as col4,
             MAX(CASE WHEN seqnum = 2 THEN value END) as col5,
             MAX(CASE WHEN seqnum = 3 THEN value END) as col6,
             MAX(CASE WHEN seqnum = 4 THEN value END) as col7
      FROM (SELECT s.value,
                   ROW_NUMBER() OVER (ORDER BY CHARINDEX('|' + id.col3 + '|', '|' + s.value + '|')) as seqnum
            FROM string_split(id.col3, '|')  s
           ) x
     ) x;

Note that this approach does not have an outer aggregation (so it should be faster than doing an outer aggregation). Instead, APPLY basically handles each row independently.

I do not have string_split function as I am not using sql server 2016 or later... — vaibhav shah, Jul 15 '20 at 12:50
@vaibhavshah . . . Your question mentions a split function in the edit. You can easily find one searching on the web — Gordon Linoff, Jul 15 '20 at 12:58

Get data into different columns from single pipe separated column

2 Answers2