split table row depending on first column

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

Answer 1

User-1174608757 posted

Hi [e-mail],

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