SQL Server : transponation same like in excel

I have a table like this with over 100 rows (different times contains in column 7).

In my example I have 2 times BOB and 3 times EVA but eva or different name can be 10times(10rows with EVA) in there.

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7
DOG         TAP KAP 28  7       672 BOB
MOUSE   XY  XY  XY      16  672     BOB
DOG         TAP KAP 6   5       525 EVA
MOUSE   XY      XY  XY  2   525 EVA
CAT     ZY      XY  XY  1   525 EVA

I expect result like this:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5  COLUMN6 COLUMN7  COLUMN8  COLUMN9  COLUMN10  COLUMN11 COLUMN12  COLUMN13  COLUMN14  COLUMN15 COLUMN16 COLUMN 17
DOG TAP KAP 28  7        MOUSE      XY       XY       XY      16    NULL     NULL      NULL      NULL       NULL      672   BOB                            
DOG TAP KAP 6   5        MOUSE      XY       XY       XY       2        CAT      ZY        XY        XY          1        525   EVA       

I tried transponate in Excel like so:

=when(long(A2) > 0, A2, E1)    

even I can do in Excel.

Thanks for opinions

1 Answer

  1. Martin- Reply

    2019-11-14

    The logic looks like this

    ;with cte as
    (select t.*,
        row_number() over (partition by column7 order by id) rn
    from t
    )
    select 
           max(column1),max(column2),max(column3),max(column4),max(column5),
           max(column8),max(column9),max(column10),max(column11),max(column12),
           max(column15),max(column16),max(column17),max(column18),max(column19),
           column100,column101
    from
    (       
    select 
        column6 as column100, column7 as column101,
    
        case when cte.rn = 1 then cte.column1 end as column1,
        case when cte.rn = 1 then cte.column2 end as column2,
        case when cte.rn = 1 then cte.column3 end as column3,
        case when cte.rn = 1 then cte.column4 end as column4,
        case when cte.rn = 1 then cte.column5 end as column5,
        case when cte.rn = 1 then cte.column6 end as column6,
        case when cte.rn = 1 then cte.column7 end as column7,
    
        case when cte.rn = 2 then cte.column1 end as column8,
        case when cte.rn = 2 then cte.column2 end as column9,
        case when cte.rn = 2 then cte.column3 end as column10,
        case when cte.rn = 2 then cte.column4 end as column11,
        case when cte.rn = 2 then cte.column5 end as column12,
        case when cte.rn = 2 then cte.column6 end as column13,
        case when cte.rn = 2 then cte.column7 end as column14,
    
        case when cte.rn = 3 then cte.column1 end as column15,
        case when cte.rn = 3 then cte.column2 end as column16,
        case when cte.rn = 3 then cte.column3 end as column17,
        case when cte.rn = 3 then cte.column4 end as column18,
        case when cte.rn = 3 then cte.column5 end as column19,
        case when cte.rn = 3 then cte.column6 end as column20,
        case when cte.rn = 3 then cte.column7 end as column21
    
    from cte
    ) s
    group by s.column100,s.column101
    

    But since you don't know the number of rows per name you need dynamic sql for example

    declare @maxrn int
    declare @i int
    
    set @maxrn = (select max(rn)  from
    (select row_number() over (partition by column7 order by id) rn from t) s
    )
    --select @maxrn
    set @i = 0
    --select 'i=' , @i
    declare @sqlstmt nvarchar(max) 
    set @sqlstmt = ';with cte as
    (select t.*,
        row_number() over (partition by column7 order by id) rn
    from t
    )
    select '
    
    while @i< @maxrn
    begin
        set @i = @i + 1
        --select 'i=' , @i
        set @sqlstmt = 
        concat(@sqlstmt,(
        select concat(
    
        'max(column' ,@i * 7 - 6, ') as column', @i * 7 - 6, ',',
        'max(column' ,@i * 7 - 5, ') as column', @i * 7 - 5, ','
        )
        )
        )
    
    end 
    
    
    set @sqlstmt= concat(@sqlstmt, 'column', @maxrn * 7 + 10,' ,column' ,@maxrn * 7 + 11, ' from
    (       
    select 
    column6 as column',@maxrn * 7 + 10,',',' column7 as column',@maxrn * 7 + 11,','
    )
    --select @sqlstmt
    set @i = 0
    while @i< @maxrn
    begin
        set @i = @i + 1
        --select 'i=' , @i
        set @sqlstmt = 
        concat(@sqlstmt,(
        select concat(
    
        'case when cte.rn = ' ,@i , ' then cte.column1 end as column', @i * 7 - 6,',',
        'case when cte.rn = ',@i,  ' then cte.column2 end as column', @i * 7 - 5 ,','
        )
        )
        )
    
    end 
    
    set @sqlstmt = concat(substring(@sqlstmt,1,len(@sqlstmt) - 1), ' from cte
    ) s
    group by s.column' , @maxrn * 7 + 10, ', s.column', @maxrn * 7 + 11
    )
    
    --select  @sqlstmt 
    exec sp_executesql @sqlstmt
    

    Result

    column1    column2    column8    column9    column15   column16   column31   column32
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    DOG        TAP        MOUSE      XY         NULL       NULL       672        BOB
    DOG        TAP        MOUSE      XY         CAT        ZY         525        EVA
    Warning: Null value is eliminated by an aggregate or other SET operation.
    

    Note I have assumed there is an identity column in the data and I have not included all the columns (to keep the example down to a digestible size). The first bit works out the max rows per user, from this I can work out how many columns there will be in the output and what their names will be (assuming there are only 7 columns per row). Once the statement is built it is executed using sp_executesql.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>