I just wrote a query (for SQL Server) that is returning this output:

But I actually need the following output

I am new to SQL Server, but I saw that this might be possible by using two PIVOTs. I really need your help to find the right syntax.

scenario and output

CodePudding user response：

You just need to pivot twice and combine the results, e.g.:

-- Setup example data...
drop table if exists #Example;
create table #Example (
  VendorId int,
  Category varchar(10),
  FirstSaleDate date,
  StoreId int
);

insert #Example (VendorId, [Category], FirstSaleDate, StoreId)
values
  (1, 'Car', '2021-01-01', 12),
  (1, 'Clothes', '2021-01-02', 13),
  (1, 'Toys', '2021-01-03', 14),
  (1, 'Food', '2021-01-04', 15),
  (1, 'Others', '2021-01-05', 15);

-- Pivot data...
with FirstSales as (
  select VendorId, Category, FirstSaleDate from #Example
), Stores as (
  select VendorId, 'StoreId_'   Category as Category, StoreId from #Example
)
select
  FirstSales.VendorId,
  Car, StoreId_Car,
  Clothes, StoreId_Clothes,
  Toys, StoreId_Toys,
  Food, StoreId_Food,
  Others, StoreId_Others
from (
  select VendorId, Car, Clothes, Toys, Food, Others
  from FirstSales
  pivot (min(FirstSaleDate) for Category in ([Car], [Clothes], [Toys], [Food], [Others])) as pvt
) as FirstSales
join (
  select VendorId, StoreId_Car, StoreId_Clothes, StoreId_Toys, StoreId_Food, StoreId_Others
  from Stores
  pivot (min(StoreId) for Category in ([StoreId_Car], [StoreId_Clothes], [StoreId_Toys], [StoreId_Food], [StoreId_Others])) as pvt
) as Stores on Stores.VendorId=FirstSales.VendorId;