Pivot table

Question

Hi my data looks like below

I want my reports like below

Answer 1

Hi @s imam

You can change your dataset like this：

create table tabledemo  
([year] int ,  
 [city] varchar(20),  
 [zip] varchar(20))  
  
 insert into tabledemo  
 select 2018,'CHI','773'  
 UNION ALL  
 SELECT 2018,'VA','703'  
  UNION ALL  
 SELECT 2018,'MA','777'  
  UNION ALL  
 SELECT 2019,'TX','703'  
  UNION ALL  
 SELECT 2019,'NY','703'  
  UNION ALL  
 SELECT 2020,'PA','333'  
  UNION ALL  
 SELECT 2020,'SA','111'  
  
   UNION ALL  
 SELECT 2020,'HOU','333'  
  UNION ALL  
 SELECT 2020,'WA','111'  
  
 with cte as(SELECT * , groupby = ROW_NUMBER()over(partition by [year] order by (select 1)) FROM tabledemo)  
 select * from cte   
 unpivot  
 ([value] for [type] in([city],[zip]))up  

the result ：

report design：

preview：

then you can delete the groupby column like this：

final preview：

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

Pivot table

For this you can use the T-SQL build-in function PIVOT, see
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Answer 3

Thank you everyone, really appreciate it.