pivot a table

Question

I have a table that has a date column and 3 columns that have values that I would like to transpose.

I would like it look like this

Answer 1

Such problems were already solved. (For example: https://learn.microsoft.com/en-us/answers/questions/513860/query-to-change-vertical-to-orizontal.html).

Try this script too:

declare @cols varchar(max) = (select string_agg(quotename([Date]), ',') within group (order by [Date]) from MyTable)  
  
/*  
In older SQL, use this instead:  
  
declare @cols varchar(max) = stuff((  
    select ',' + quotename([Date])  
    from MyTable  
    order by [Date]  
    for xml path('')  
), 1, 1, '')  
*/  
  
declare @sql varchar(max) = concat(  
'select c as [ ], ', @cols, '  
from (  
    select [date], Col1, Col2, Col3  
    from MyTable  
) t  
unpivot  
(  
    v for c in (Col1, Col2, Col3)  
) u  
pivot  
(  
    max (v) for [Date] in (', @cols, ')  
) p')  
  
--print @sql  
  
exec (@sql)  

Answer 2

Hi @ojmp2001 ojmp2001 ,

Please also refer below:

create table tableo  
([date] date,  
col1 int,  
col2 int,   
col3 int)  
  
insert into tableo values  
('1/1/2020',100,500,100),  
('2/1/2020',150,400,300),  
('3/1/2020',175,450,200)  
  
Select * from (  
select [date],COL,VAL from tableo  
CROSS APPLY (VALUES ('col1',col1),  
('col2',col2),  
('col3',col3))CS (COL,VAL))T  
PIVOT (MAX(VAL)   
FOR [date] IN ([2020-01-01],[2020-02-01],[2020-03-01]))PVT  

Or dynamic way as below:

declare @sql nvarchar(max)  
      
select @sql=STUFF(( SELECT distinct  ',['+convert(varchar(10),[date])+']'  FROM tableo FOR XML PATH('') ), 1, 1, '')  
  
set @sql=N' Select * from (  
select [date],COL,VAL from tableo  
CROSS APPLY (VALUES (''col1'',col1),  
(''col2'',col2),  
(''col3'',col3))CS (COL,VAL))T  
PIVOT (MAX(VAL)   
FOR [date] IN ('+@sql+')) PVT'  
      
EXECUTE sp_executesql  @sql  

Output:

COL	2020-01-01	2020-02-01	2020-03-01  
col1	100	150	175  
col2	500	400	450  
col3	100	300	200  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.