locked
how to get multi columns from single column value RRS feed

  • Question

  • Dear All.

    I have a table with one column A value (1;13004211,13004211_02_13004212,40000000,1105,2000)

    I want output like below in sql?

    column 1 column 2        column 3
    column 4
    13004211 13004211_02_13004212      40000000 1105





    Thanks

    HadoopHelp



    Friday, March 6, 2020 1:12 PM

All replies

  • Hi 

    We have moved this to Transact SQL Forum to get more traction for your issue.

    Thanks
    Navtej S

    Friday, March 6, 2020 3:54 PM
  • Use a split function

    check https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b920ad4-b9fc-4ae2-b401-724cecd4e1a5/am-i-blind-why-is-this-not-working?forum=transactsql

    to get the split function posted by Tom:

    --CREATE FUNCTION [dbo].[split](
    --          @delimited NVARCHAR(MAX),
    --          @delimiter NVARCHAR(100)
    --        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
    --	   WITH SCHEMABINDING
    --        AS
    --        BEGIN
    --          DECLARE @xml XML
    --          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    
    --          INSERT INTO @t(val)
    --          SELECT  r.value('.','nvarchar(MAX)') as item
    --          FROM  @xml.nodes('/t') as records(r)
    --          RETURN
    --        END
    
    create table test(colA varchar(200))
    insert into test values('1;13004211,13004211_02_13004212,40000000,1105,2000)')
    
    select val ,stuff(val,1,charindex(';',val),'') from test
    cross apply   dbo.split(colA,',') 
    WHERE id<=4
    
    drop table test

    Friday, March 6, 2020 4:31 PM
  • create table test(colA varchar(200))
    insert into test values('1;13004211,13004211_02_13004212,40000000,1105,2000)')
    
     
    
    
    
    --XML
    ;WITH mycte AS
    (
    SELECT  colA,Cast(N'<H><r>' + 
    REPLACE(colA, ',', '</r><r>')+ '</r></H>' as XML)  AS vals
    FROM test
    )
      
    SELECT colA
    ,stuff(vals.value('(/H/r)[1]','VARCHAR(30)'),1,charindex(';',vals.value('(/H/r)[1]','VARCHAR(30)')),'') col1
         , vals.value('(/H/r)[2]','VARCHAR(30)') AS col2
        , vals.value('(/H/r)[3]','VARCHAR(30)') AS col3
        , vals.value('(/H/r)[4]','VARCHAR(30)') AS col4     From mycte
    
    
    
    
    
    --SQL Server 2016  or up
    --JSON
     select colA
     ,stuff(JSON_VALUE(value, '$.csvdata[0]'),1,charindex(';',JSON_VALUE(value, '$.csvdata[0]')),'') col1
    ,JSON_VALUE(value, '$.csvdata[1]') col2
    ,JSON_VALUE(value, '$.csvdata[2]') col3
    ,JSON_VALUE(value, '$.csvdata[3]') col4
      
     from (
    Select colA
    ,'[{"csvdata":["'+ REPLACE(colA, ',', '","') + '"]} ] '   as vals
    FROM   test
    ) t
    cross apply openjson(vals) d 
    
    
    
    drop table test

    Friday, March 6, 2020 6:58 PM
  • Hi HadoopHelp, 

    I do a little change in Jingyang Li's script .  Please check following script. 

    CREATE FUNCTION [dbo].[split](
              @delimited NVARCHAR(MAX),
              @delimiter NVARCHAR(100)
            ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
    	   WITH SCHEMABINDING
            AS
            BEGIN
              DECLARE @xml XML
              SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    
              INSERT INTO @t(val)
              SELECT  r.value('.','nvarchar(MAX)') as item
              FROM  @xml.nodes('/t') as records(r)
              RETURN
            END
    go
    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test(colA varchar(200))
    insert into test values('1;13004211,13004211_02_13004212,40000000,1105,2000)')
    
    ;with cte as (
    select id ,stuff(val,1,charindex(';',val),'') val from test
    cross apply   dbo.split(colA,',') 
    WHERE id<=4)
    select [1] [column 1], [2] [column 2], [3] [column 3], [4] [column 4]
    from cte 
    PIVOT (
    	MAX(val)
    	FOR id IN ([1], [2], [3], [4])
    ) AS pvt
    /*
    column 1     column 2                column 3  column 4
    ------------ ----------------------- --------- ------------
    13004211     13004211_02_13004212    40000000  1105
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, March 9, 2020 6:05 AM
  • with ect1 as(
     select '1;13004211,13004211_02_13004212,40000000,1105,2000' as col1
     ),
     ect2 as
     (
     select SUBSTRING(ect1.col1, CHARINDEX(';',ect1.col1)+1,CHARINDEX(',',ect1.col1) - 2) as col1 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum,
     SUBSTRING(ect1.col1, CHARINDEX(';',ect1.col1)+1,LEN(ect1.col1)-2) as col3,
     ),
     ect3 as
     (
     select SUBSTRING(ect2.col3,0, CHARINDEX(',',ect2.col3) - 1) as col1 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum,
     SUBSTRING(ect1.col1, CHARINDEX(',',ect2.col3)+1,LEN(ect2.col3)-2) as col3,
     ),
    ect4 as
     (
     select SUBSTRING(ect3.col3,0, CHARINDEX(',',ect3.col3) - 1) as col1 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum,
     SUBSTRING(ect3.col1, CHARINDEX(',',ect3.col3)+1,LEN(ect3.col3)-2) as col3,
     ),
    ect5 as
     (
     select SUBSTRING(ect4.col3,0, CHARINDEX(',',ect4.col3) - 1) as col1 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum,
     SUBSTRING(ect4.col1, CHARINDEX(',',ect4.col3)+1,LEN(ect4.col3)-2) as col3,
     )
    
    
    select a.col1,b.col1,c.col1,d.col1 from ect2 a
    innder join ect3 b on a.rownum = b. rownum
    innder join ect4 c on a.rownum = c. rownum
    innder join ect5 d on a.rownum = d. rownum
    

    Monday, March 9, 2020 7:21 AM
  • DECLARE @s VARCHAR(500)=' 1;13004211,13004211_02_13004212,40000000,1105,2000'

    SELECT
    SUBSTRING(@s,4,T2.col1-4) AS col1,
    SUBSTRING(@s,T2.col1+1,T3.col1-T2.col1-1) AS col2,
    SUBSTRING(@s,T3.col1+1,T4.col1-T3.col1-1) AS col3,
    SUBSTRING(@s,T4.col1+1,T5.col1-T4.col1-1) AS col4

    FROM
    (VALUES (@s)) AS T1(col1)
    CROSS APPLY
    (VALUES(CHARINDEX(',', T1.col1))) AS T2(col1)
    CROSS APPLY
    (VALUES(CHARINDEX(',', T1.col1, T2.col1 + 1))) AS T3(col1)
    CROSS APPLY
    (VALUES(CHARINDEX(',', T1.col1, T3.col1 + 1))) AS T4(col1)
    CROSS APPLY
    (VALUES(CHARINDEX(',', T1.col1, T4.col1 + 1))) AS T5(col1)
    CROSS APPLY
    (VALUES(CHARINDEX(',', T1.col1, T5.col1 + 1))) AS T6(col1);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 9, 2020 7:39 AM
    Answerer
  • Thanks All.

    but above all solution will not fit in my case.

    because i have already select query and wanted for only single columns but fitting the above query little bit complex for me.

    i want simple ,single line query so that  can fit with my existing select query.

    Thanks

    HadoopHelp

    Monday, March 9, 2020 10:21 AM
  • The solutions were based on your posted requirement.

    We don't know what your current query is.

    You can use posted solutions to create a tabel valued UDF and use it in your existing query.

    If you need more help, you need to provide more information about what you are trying to do. Thanks.

    Monday, March 9, 2020 1:29 PM
  • I have a table with one column A value (1;13004211,13004211_02_13004212,40000000,1105,2000)

    When we wrote the SQL standards, we never intended to do this sort of data formatting in the database level. What you're supposed to do instead is create a presentation layer for the input, get your data looking good and then pass it down to the database. This concept is called a tiered architecture and it's been the standard for programming since the 1970s. Would you like some stinking dirty kludge is, so you can keep being a bad programmer or can you fix things at the application layer?


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 9, 2020 8:10 PM
  • Hi HadoopHelp,

    Sorry that our script could not solve your issue. Per your original description , most of us have provided some script to achieve your requirement. Now , your requirement might be unclear. 

    Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 5:33 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 12, 2020 7:53 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 17, 2020 7:18 AM