locked
split table row depending on first column RRS feed

  • Question

  • User-1634604574 posted

    i have this table dynamically not by static

    col1                        col2

    colour                    red

    colour                    green

    colour                   blue

    size                        large

    size                       small

    size                       x-large

    i want to display like this

    colour                 size

    red                     large

    green                small

    blue                  x-large

    Monday, January 21, 2019 12:00 PM

All replies

  • User-1174608757 posted

    Hi zhyanadil.it@gmail.com,

    According to your description, Could you please tell me what does dynamically means in  your description?  Is colour or size is not sure ?  If you could post more details about your requirement , it will help us solve your question quickly .Here is a demo, I hope it could help you.

    CREATE TABLE cs(
       col1 VARCHAR(50) NOT NULL  
      ,col2 VARCHAR(50) NOT NULL
    );
    INSERT INTO cs(col1,col2) VALUES
     ('colour','red')
    ,('colour','green')
    ,('colour','blue')
    ,('size','large')
    ,('size','small')
    ,('size','x-large');
     WITH cte as (select ROW_NUMBER() over (partition by col1 order by col1) as rownum, col1, col2 from cs)
    select col2 as colour , 
    (select col2 from cte where rownum= table1.rownum and col1 ='size') as size
    from cte as table1 where col1='colour' 
    drop table cs

    It shows as below:

    Best Regards

    Wei Zhang

    Tuesday, January 22, 2019 5:55 AM