how to write this query?

• water490

  Ten Centuries

  Points: 1215

  More actions

  March 15, 2026 at 4:17 am

  #4748883

  Hi everyone

  I asked this earlier but the desired outcome is a bit different this time so I am starting a new post.  I would like to transform the source table into the desired outcome format.  How can I do this?

  Desired Outcome:

  

  Sample Data:

  drop table if exists #temp1
  
  create table #temp1
  (
  report_run_time datetime not null,
  sales int not null,
  continent varchar(50) not null,
  country varchar(50) not null,
  city varchar(50) not null
  )
  
  insert into #temp1
  values ('2026-03-13 14:24:16.393','200','Asia','Japan','Tokyo'),
     ('2026-03-13 14:24:16.393','150','Asia', 'India','Chandigarh'),
     ('2026-03-13 14:24:16.393','395','Asia', 'Philippines','Manila'),
     ('2026-03-13 14:24:16.393','105','Europe', 'Switzerland ','Lucerne'),
     ('2026-03-13 14:24:16.393','225','Europe', 'Italy ','Rome'),
     ('2026-03-14 14:24:16.393','145','Asia','Japan','Tokyo'),
     ('2026-03-14 14:24:16.393','385','Asia', 'India','Chandigarh'),
     ('2026-03-14 14:24:16.393','95','Asia', 'Philippines','Manila'),
     ('2026-03-14 14:24:16.393','175','Europe', 'Switzerland ','Lucerne')

  Thank you

• Phil Parkin

  SSC Guru

  Points: 247228

  More actions

  March 15, 2026 at 9:45 am

  #4748966

  Assuming you want your output columns to be dynamic, based on the input data, this really is best achieved in a reporting tool.

  What would your desired output look like if the input data also included (Asia, India, Mumbai)? Presumably also grouped under Asia India? T-SQL queries can't do merged cells.

  ---

• pietlinden

  SSC Guru

  Points: 63642

  More actions

  March 16, 2026 at 11:26 pm

  #4749714

  Your sample desired output looks a lot like a PowerPivot in Excel. So I don't think trying to write a query for this is maybe what you want. (or is it?) If you're looking to drill into the data, I'd do it in PowerBI (the UI in Excel isn't my favorite for matrixes.

  continent varchar(50) not null,

  country varchar(50) not null,

  city varchar(50) not null

  is essentially a geography/sales territory kind of hierarchy. So what if you put the hierarchy in columns, dates hierarchy on rows, and then totals or whatever on values.

  That said, I slapped up a quick matrix in PowerBI...

  1. Two tables:

     1. (fact) SalesReporting (city, report run time, sales)
     2. (dimension) SalesTerritory (city, country, continent)
     3. ... join them on city. (shocker, right?)

  2. create a hierarchy of City, Country, Continent, call it (maybe) Geography Hierarchy
  3. create a measure, Total Sales:= SUM('Sales Reporting'[Sales])

  then drop in a matrix

  on ROWS: report_run_time

  on COLUMNS: Geography Hierarchy

  on VALUES: [Total Sales]

  done. =)

   

  • This reply was modified 11 hours, 58 minutes ago by pietlinden. Reason: more information/clarification
  • This reply was modified 11 hours, 49 minutes ago by pietlinden.