How to Split One Column into Two Columns in SQL Server

Split one column into multiple columns in SQL Server

Assuming you are using SQL Server (as mentioned in the title), you can use the following query to split the full name into last, first, and middle:

select substring(FullName, 1, patindex('%,%', FullName) - 1) as LastName,
substring(
  FullName, 
  charindex(', ', FullName) + 2, 
  charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2) as FirstName,
substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)) as MiddleName
from Employee;

This will output:

LastName  |  FirstName  |  MiddleName
-------------------------------------
  Holmes  |       John  |       Henry

[EDIT]

In order to update your existing table such that full name is saved as last, first, and middle, you need to:

(1) Alter the table by adding FirstName, MiddleName, and LastName columns:

alter table Employee 
add FirstName varchar(50), 
    MiddleName varchar(50),
    LastName varchar(50);

(2) Update the table by breaking the full name into last, first, and middle:

update Employee
set FirstName = substring(
    FullName, 
    charindex(', ', FullName) + 2, 
    charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2),
  MiddleName = substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)),
  LastName = substring(FullName, 1, patindex('%,%', FullName) - 1);

(3) Drop the FullName column:

alter table Employee drop column FullName;

Single query to split out data of one column, into two columns, from the same table based on different criteria [SQL]

This is quite ugly, but doable. As a starter, you need a column that defines the order of the rows - I assumed that you have such a column, and that is called id.

Then you can select the distinct texts, put them in separate groups depending on their length, and finally pivot:

select
    max(case when grp = 0 then dataText end) shortData,
    max(case when grp = 1 then dataText end) longData
from (
    select 
        dataText, 
        grp,
        row_number() over(partition by grp order by id) rn
    from (
        select
            id,
            case when len(dataText) <= 13 then 0 else 1 end grp,
            substring(dataText, 1, 13) dataText 
        from (select min(id) id, dataText from mytable group by dataText) t
    ) t
) t
group by rn

If you are content with ordering the records by the string column itself, it is a bit simpler (and, for your sample data, it produces the same results):

select
    max(case when grp = 0 then dataText end) shortData,
    max(case when grp = 1 then dataText end) longData
from (
    select 
        dataText, 
        grp,
        row_number() over(partition by grp order by dataText) rn
    from (
        select distinct
            case when len(dataText) <= 13 then 0 else 1 end grp,
            substring(dataText, 1, 13) dataText 
        from mytable
    ) t
) t
group by rn

Demo on DB Fiddle:

shortData   | longData     
:---------- | :------------
1 DEC20 DDD | 1 JUL20 DDD80
1 JAN21 DDD | 1 JUL20DDDD40
1 JUL20 DDD | 1 JUL20DDDD80
null        | 1 JUN20 DDD50
null        | 1 JUN20DDDD50

Split an sql column into two based on another columns values

You can use CROSS JOIN and get cartesian :

SELECT * FROM (
  SELECT  value, identifier
  FROM [tableName]
  WHERE identifier = 'Collection_Image'
) Images
CROSS JOIN (
  SELECT  value, identifier
  FROM [tableName]
  WHERE identifier = 'Collection_Title'
) Titles;

Result:

+============+==================+=============+==================+
| value      | identifier       | value       | identifier       |
+============+==================+=============+==================+
| Image_URL1 | Collection_Image | Title_Name1 | Collection_Title |
+------------+------------------+-------------+------------------+
| Image_URL2 | Collection_Image | Title_Name1 | Collection_Title |
+------------+------------------+-------------+------------------+
| Image_URL1 | Collection_Image | Title_Name2 | Collection_Title |
+------------+------------------+-------------+------------------+
| Image_URL2 | Collection_Image | Title_Name2 | Collection_Title |
+------------+------------------+-------------+------------------+

SQL cartesian fiddle

You also can use ROW_NUMBER function as join condition :

SELECT Images.value, Images.identifier, Titles.value, Titles.identifier  
FROM (
  SELECT  value, identifier, row_number() over(order by value) rn
  FROM [tableName]
  WHERE identifier = 'Collection_Image'
) Images
FULL OUTER JOIN (
  SELECT  value, identifier, row_number() over(order by value) rn
  FROM [tableName]
  WHERE identifier = 'Collection_Title'
) Titles ON Images.rn = Titles.rn;

FULL OUTER JOIN

Split data within one column into two columns

If you are SQL Server version 2016 or later you can use OPENJSON()

DECLARE @testdata TABLE
    (
        [id] INT
      , [pid] BIGINT
      , [code] INT
      , [class] NVARCHAR(100)
      , [sclass] NVARCHAR(100)
      , [name] NVARCHAR(100)
      , [coordinates] NVARCHAR(MAX)
    );

INSERT INTO @testdata (
                          [id]
                        , [pid]
                        , [code]
                        , [class]
                        , [sclass]
                        , [name]
                        , [coordinates]
                      )
VALUES ( 24079
       , 4273753
       , 5122
       , 'Roads'
       , 'residential'
       , 'Apoteksgatan'
       , '[ [ [ 58.26574, 12.9477909 ], [ 58.2643948, 12.9490654 ], [ 58.2640484, 12.9494174 ], [ 58.2638978, 12.9498716 ], [ 58.2637401, 12.9505464 ], [ 58.2634622, 12.9518316 ] ] ]' );

SELECT      [a].[id]
          , [d].[Key]
          , [d].[Value]
FROM        @testdata [a]
CROSS APPLY OPENJSON([a].[coordinates]) [b]
CROSS APPLY OPENJSON([b].[Value]) [c]
CROSS APPLY OPENJSON([c].[Value]) [d];

Which gives you the following results

id          Key Value
----------- --- -----------
24079       0   58.26574
24079       1   12.9477909
24079       0   58.2643948
24079       1   12.9490654
24079       0   58.2640484
24079       1   12.9494174
24079       0   58.2638978
24079       1   12.9498716
24079       0   58.2637401
24079       1   12.9505464
24079       0   58.2634622
24079       1   12.9518316

---

Related Topics