Pivot table

s imam 21 Reputation points
2022-04-07T22:27:15.05+00:00

Hi my data looks like below

191143-image.png

I want my reports like below

191105-image.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,950 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
{count} votes

Accepted answer
  1. Isabellaz-1451 3,616 Reputation points
    2022-04-08T04:16:22.817+00:00

    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 :
    191134-image.png

    report design:
    191212-image.png
    preview:
    191049-image.png
    then you can delete the groupby column like this:
    191050-image.png
    final preview:
    191213-image.png

    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.

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 45,366 Reputation points
    2022-04-08T05:59:28.94+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. s imam 21 Reputation points
    2022-04-11T02:25:39.213+00:00

    Thank you everyone, really appreciate it.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.