Home 2018 February SQL Puzzle: SQL Advance Query – Divide a Column data into Multiple Columns

SQL Puzzle: SQL Advance Query – Divide a Column data into Multiple Columns

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to divide a column data into multiple columns in SQL Server.

Input data:

Expected Output:

Create a table with sample data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: How to change Authentication Mode from Windows Registry?SQL Server Interview: Identify the main table name from SQL Queries
Comments: 4
  1. Vimal Lohani
    February 15, 2018 at 6:13 am

    Please check below query-

    CREATE TABLE #table(id int, coln varchar(100))

    INSERT INTO #table VALUES (1,’one two three four’)
    INSERT INTO #table VALUES (2,’script is written by dbavimal’)

    SELECT * FROM #table
    DECLARE @pivot varchar(8000)
    DECLARE @select varchar(8000)
    DECLARE @Counter int=0
    DECLARE @DATA INT =(SELECT max(len(coln)-len(replace(coln,’ ‘,”))) FROM #table)

    WHILE @DATA>=0
    Begin
    Set @Counter=@Counter+1
    Set @pivot=coalesce(@pivot+’,’,”)+'[col’+cast(@Counter as varchar(10))+’]’
    set @DATA=@DATA-1
    End

    SELECT
    @select=’
    select v.*
    from (
    select
    id,substring(coln, start+2, endPos-Start-2) as token,
    ”col”+cast(row_number() over(partition by id order by start) as varchar(10)) as n
    from (
    select
    id, coln, n as start, charindex(” ”,coln,n+2) endPos
    from (select number as n from master..spt_values where type=”p”) num
    cross join
    (
    select
    id, ” ” + coln +” ” as coln
    from
    #table
    ) m
    where n < len(coln)-1
    and substring(coln,n+1,1) = '' '') as coln
    ) pvt
    Pivot ( max(token)for n in ('+@pivot+'))v'

    EXEC(@select)

    –print @select
    drop table #table

    ReplyCancel
  2. Yasir
    March 9, 2018 at 8:48 am

    select *, substring(col1,1,charindex(‘ ‘,col1)-1),
    substring(col1,charindex(‘ ‘,col1)+1,charindex(‘ ‘,col1)),
    reverse(substring(reverse(col1),1,charindex(‘ ‘,reverse(col1))-1))
    from tbl_SplitColumns

    ReplyCancel
  3. Lakshman
    May 5, 2019 at 10:40 am

    select Col1,Col2,Col3 from
    (SELECT CAST(‘‘+REPLACE(col1,’ ‘,’‘)+’‘ AS XML) AS a FROM tbl_SplitColumns) t
    CROSS APPLY
    (
    SELECT DISTINCT X.v.value(‘(/A/node())[1]’,’nvarchar(50)’) as COL1
    ,X.v.value(‘(/A)[2]’,’nvarchar(50)’) as COL2
    ,X.v.value(‘(/A)[3]’,’nvarchar(50)’) as COL3
    FROM t.a.nodes(‘/A’) AS X(v)
    )z

    SELECT PARSENAME(Split,3) Col1,PARSENAME(Split,2) Col2,PARSENAME(Split,1) Col3 FROM(
    SELECT REPLACE(col1,’ ‘,’.’) AS Split FROM tbl_SplitColumns) Z

    ReplyCancel
  4. Phanindra Suripeddi
    August 2, 2019 at 7:37 am

    DECLARE @tbl_SplitColumns TABLE (col1 varchar(max))

    INSERT INTO @tbl_SplitColumns
    VALUES (‘part1 part2 part3’),(‘abc xyz pqr’),(‘one two three’)

    ;WITH T AS
    (
    SELECT Row_Number() OVER(ORDER BY (SELECT 1)) Rno, col1 From @tbl_SplitColumns
    )
    ,T2 As
    (
    SELECT Rno,n=1, i=CAST(1 AS INT), j=CHARINDEX(‘ ‘,col1), col1+’ ‘ AS Col1 FROM T
    UNION ALL
    SELECT Rno,n=n+1,i=CAST(j+1 AS INT), j=CHARINDEX(‘ ‘,col1, j+1), col1 FROM T2 WHERE j>0
    )
    ,T3 as
    (
    Select Rno,n, SUBSTRING(col1,i,j-i+1) st FROM T2 where j>0
    )
    select a.st as Col1, b.st as col2, c.st as Col3 from T3 a
    inner join T3 b on b.Rno=2 and a.n=b.n
    inner join T3 c on c.Rno=3 and a.n=c.n
    where a.Rno=1
    Order by a.Rno, a.n

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

February 14, 2018 4 Comments SQL Puzzle, , , , , , , , , ,