none
Row to Columns in SQL

    질문

  • Hi everyone,

    I am trying to get rows converted to columns. Can someone help me out to this figure out? I am able to split the string using  STRING_SPLIT but I am not sure how to create kind of pivot columns in SQL Server. 

    These are my rows:

    IDMARK

    ----------------------------

    A_ROB_B_20141111

    B_ROB_A_20142545

    C_ROB_A_20145868

    I would like to split each row like the below example.

    ID              FIRST_ID     SECOND_ID      THIRD_ID          FOURTH_ID

    A_ROB_B_20141111    A          ROB                  B                      20141111

    Thank you, 

    Willmx


    nme


    • 편집됨 willmx 2018년 7월 12일 목요일 오후 9:26
    2018년 7월 12일 목요일 오후 9:24

답변

  • No could be 6 or more...


    nme

    create table test (IDMARK varchar(200))
    Insert into test values ('A_ROB_B_20141111')
    ,('B_ROB_A_20142545')
    ,('C_ROB_A_20145868')
    
     
     
     ;with mycte as ( 
    SELECT IDMARK,CAST(N'<H><r>' +  Replace(IDMARK,'_', '</r><r>') + '</r></H>' as XML) AS vals
    FROM test
     
    ) 
      
    SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1,
    S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS c2,
    S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS c3,
    S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS c4,
    S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS c5,
    S.a.value('(/H/r)[6]', 'NVARCHAR(50)') AS c6 
     , S.a.value('(/H/r)[7]', 'NVARCHAR(50)') AS c7,
     S.a.value('(/H/r)[8]', 'NVARCHAR(50)') AS c8
     -- or more
    FROM mycte d
      
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
     
    
    drop table test


    • 편집됨 Jingyang LiModerator 2018년 7월 13일 금요일 오후 2:27
    • 답변으로 표시됨 willmx 2018년 7월 13일 금요일 오후 2:48
    2018년 7월 13일 금요일 오후 2:26
    중재자

모든 응답

  • create table test (IDMARK varchar(200))
    Insert into test values ('A_ROB_B_20141111')
    ,('B_ROB_A_20142545')
    ,('C_ROB_A_20145868')
    
    Select *
    , parsename(replace(IDMARK,'_','.'),4) FIRST_ID     
    , parsename(replace(IDMARK,'_','.'),3) SECOND_ID      
    , parsename(replace(IDMARK,'_','.'),2) THIRD_ID          
    , parsename(replace(IDMARK,'_','.'),1) FOURTH_ID
    
     from test
     
     
    
    drop table test

    2018년 7월 12일 목요일 오후 9:38
    중재자
  • Hi Willmx,

    Also you can try String_Split() function with Pivot(). Please refer:

    create table #test (IDMARK varchar(200))
    
    Insert into #test values 
    ('A_ROB_B_20141111')
    ,('B_ROB_A_20142545')
    ,('C_ROB_A_20145868')
    
    select IDMARK, [1] as First_ID, [2] as Second_ID, [3] as Third_ID, [4] as Fourth_ID
    from (
    	select IDMARK,value,
    		ROW_NUMBER()Over(Partition By IDMARK Order By CHARINDEX(value,IDMARK)) as ColumnName
    	from #test 
    	cross apply string_split(IDMARK,'_') )t
    Pivot( 
    	MAX(value) 
    	for ColumnName in ([1],[2],[3],[4])
    	) pvt

    Thanks,
    Xi Jin.


    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.

    2018년 7월 13일 금요일 오전 6:01
  • can there be only four parts always?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 7월 13일 금요일 오전 6:12
  • No could be 6 or more...


    nme

    2018년 7월 13일 금요일 오후 2:21
  • No could be 6 or more...


    nme

    create table test (IDMARK varchar(200))
    Insert into test values ('A_ROB_B_20141111')
    ,('B_ROB_A_20142545')
    ,('C_ROB_A_20145868')
    
     
     
     ;with mycte as ( 
    SELECT IDMARK,CAST(N'<H><r>' +  Replace(IDMARK,'_', '</r><r>') + '</r></H>' as XML) AS vals
    FROM test
     
    ) 
      
    SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS c1,
    S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS c2,
    S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS c3,
    S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS c4,
    S.a.value('(/H/r)[5]', 'NVARCHAR(50)') AS c5,
    S.a.value('(/H/r)[6]', 'NVARCHAR(50)') AS c6 
     , S.a.value('(/H/r)[7]', 'NVARCHAR(50)') AS c7,
     S.a.value('(/H/r)[8]', 'NVARCHAR(50)') AS c8
     -- or more
    FROM mycte d
      
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
     
    
    drop table test


    • 편집됨 Jingyang LiModerator 2018년 7월 13일 금요일 오후 2:27
    • 답변으로 표시됨 willmx 2018년 7월 13일 금요일 오후 2:48
    2018년 7월 13일 금요일 오후 2:26
    중재자
  • The solution does not work if the column has 5+ I got nulls

    A_ROB_B_20141111_2    NULL    NULL    NULL    NULL    NULL
    B_ROB_A_20142545_2    NULL    NULL    NULL    NULL    NULL
    C_ROB_A_20145868_2    NULL    NULL    NULL    NULL    NULL


    nme

    • 답변으로 표시됨 willmx 2018년 7월 13일 금요일 오후 2:48
    • 답변으로 표시 취소됨 willmx 2018년 7월 13일 금요일 오후 2:48
    2018년 7월 13일 금요일 오후 2:27
  • Awesome this works!

    nme

    2018년 7월 13일 금요일 오후 2:43