SQLTeam.com | Weblogs | Forums

Rearrange Table without using PIVOT


Hello Community,

I have created the following table and fields:

--create table dbo.trx2a
--(pk_country_id varchar(2),
--fk_consumer_id varchar (50),
--wks_since int,
--fuel_type varchar(50),
--sales_quantity float)

   --insert dbo.trx2a values ('CA','consumer1',3, 'v-power',45.04)
   --insert dbo.trx2a values ('CA','consumer1',3, 'v-power',30.00)
   --insert dbo.trx2a values ('DE','consumer2',1, 'lubes',15.00)
   --insert dbo.trx2a values ('CA','consumer3',4, 'petrol',20.00)
   --insert dbo.trx2a values ('CA','consumer3',4, 'petrol',5.00)
   --insert dbo.trx2a values ('CA','consumer3',4, 'petrol',60.00)
   --select * from dbo.trx2

When I query all the fields in the table I get the following output:


Can someone help with a sql script query that when run will provide the following output:


But I need this to be accomplished without using PIVOT table if at all possible?



Just so you know the following will achieve the desired result, but need the same result without using PIVOTs

select * from dbo.trx2a

select pk_country_id, fk_consumer_id, wks_since, [v-power], [lubes], [petrol]
select pk_country_id, fk_consumer_id, wks_since , fuel_type, sales_quantity
from dbo.trx2a
) src
for fuel_type in ([v-power], [lubes], [petrol])
) piv;


Try this:

select pk_country_id
      ,sum(case when fuel_type='v-power' then sales_quantity end) as [v-power]
      ,sum(case when fuel_type='lubes' then sales_quantity end) as lubes
      ,sum(case when fuel_type='petrol' then sales_quantity end) as petrol
  from dbo.trx2a
 where fuel_type in ('v-power','lubes','petrol')
 group by pk_country_id



IT WORKED! You're a genius.

Many many many thanks



I just have one question.

Why did you put square brackets around 'v-power' and not around 'lubes' or 'petrol'?

But more importantly how come it still worked?


Because of the minus character. It might be interpreted as a formular by MSSQL engine. One could choose to always use the square brackets, but I find it more readable to avoid whenever possible.


ahhhhhh.. I see.