none
Spliting Values into multiple columns in single rows in sql RRS feed

  • Question

  • Hi,

    How can i split below code into multiple column under one ID in one rows ?

      ID            Code
    79216137   C3490
    79216137   C779
    79216137    C799     

    Example   

    ID             Code1      Code2        Code3
    79216137    C3490      C779         C799

    Thanks in advance    
       


    yoba

    Wednesday, November 13, 2019 11:16 PM

All replies

  • Hi yoba123,

    Please check following three solutions .

    IF OBJECT_ID('test') IS NOT NULL drop table  test   
    go 
    create table test 
    (ID int,
    Code varchar(10))
    insert into test values 
    (79216137,'C3490'),
    (79216137,'C779'),
    (79216137,'C799 ')  
    
    -------------1
    ;with cte as (
    select * , 'Code'+cast(row_number ()over(partition by ID order by Code) as varchar(10))  column_name
    from test
    )
    SELECT ID,
    max(CASE WHEN column_name = 'Code1' THEN Code end ) [Code1],
    max(CASE WHEN column_name = 'Code2'THEN Code end) [Code2],
    max(CASE WHEN column_name = 'Code3'THEN Code end) [Code3]
    FROM cte  
    group by ID
    /*
    ID          Code1      Code2      Code3
    ----------- ---------- ---------- ----------
    79216137    C3490      C779       C799 
    */
    
    -------------2
    ;with cte as (
    select * , 'Code'+cast(row_number ()over(partition by ID order by Code) as varchar(10))  column_name
    from test
    )
    SELECT *
    FROM cte 
    PIVOT (
    	MAX(Code)
    	FOR [column_name] IN ([Code1], [Code2], [Code3])
    ) AS pvt
    /*
    ID          Code1      Code2      Code3
    ----------- ---------- ---------- ----------
    79216137    C3490      C779       C799 
    */
    
    -------------3(dynamic)
    DECLARE @sql_str VARCHAR(8000)
    DECLARE @sql_col VARCHAR(8000)
    SELECT @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME(column_name) FROM (select distinct 'Code'+cast(row_number ()over(partition by ID order by Code) as varchar(10))  column_name
    				from test) t 
    SET @sql_str = '
    ;with cte as (
    select * , ''Code''+cast(row_number ()over(partition by ID order by Code) as varchar(10))  column_name
    from test
    )
    SELECT *
    FROM cte 
    PIVOT (
    	MAX(Code)
    	FOR [column_name] IN ('+@sql_col+')
    ) AS pvt'
    PRINT (@sql_str)
    EXEC (@sql_str)
    /*
    ;with cte as (
    select * , 'Code'+cast(row_number ()over(partition by ID order by Code) as varchar(10))  column_name
    from test
    )
    SELECT *
    FROM cte 
    PIVOT (
    	MAX(Code)
    	FOR [column_name] IN ([Code1],[Code2],[Code3])
    ) AS pvt
    ID          Code1      Code2      Code3
    ----------- ---------- ---------- ----------
    79216137    C3490      C779       C799 
    */
    

    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, November 14, 2019 2:31 AM
  • --SQL server 2017 or 2019
    --Maximum number of each group is 4
    
    ;with mycte as (select ID 
    ,string_agg(Code,'.') within group(Order by try_cast(stuff(Code,1,1,'') as int) DESC) aggCode
     from test
    GROUP BY ID)
    
    select ID, aggCode
    ,parsename(aggCode,1) code1 
    ,parsename(aggCode,2) code2
    ,parsename(aggCode,3) code3
    --,parsename(aggCode,4) code4 
    from mycte

    Thursday, November 14, 2019 3:23 AM
    Moderator
  • A solution for up to three codes, which is based on LEAD:

     

    declare @MyTable as table

    (

        ID int,

        Code varchar(20)

    )

     

    insert into @MyTable values

        ( 79216137, 'C3490' ),

        ( 79216137, 'C779' ),

        ( 79216137, 'C799' ),

        ( 88888888, 'C3490' ),

        ( 99999999, 'C5555' ),

        ( 99999999, 'C779' ),

        ( 99999999, 'C5555' )

     

    ; with Q as

    (

        select ID, Code,

            LEAD(Code, 1) over( partition by ID order by Code ) as Code2,

            LEAD(Code, 2) over( partition by ID order by Code ) as Code3,

            ROW_NUMBER() over ( partition by ID order by Code ) as rn

        from @MyTable

    )

    select ID, Code as Code1, Code2, Code3

    from Q

    where rn = 1

     

    /*

    ID        Code1  Code2  Code3

    79216137  C3490  C779   C799

    88888888  C3490  NULL   NULL

    99999999  C5555  C5555  C779

    */



    Thursday, November 14, 2019 6:14 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.

    Tuesday, November 19, 2019 8:44 AM