Rearrange Table without using PIVOT

Transact-SQL
1

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:

alltable

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

nopivot

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

Thanks

2

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]
from
(
select pk_country_id, fk_consumer_id, wks_since , fuel_type, sales_quantity
from dbo.trx2a
) src
pivot
(
sum(sales_quantity)
for fuel_type in ([v-power], [lubes], [petrol])
) piv;

3

Try this:

select pk_country_id
      ,fk_consumer_id
      ,wks_since
      ,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
         ,fk_consumer_id
         ,wks_since
;
4

bitsmed,

IT WORKED! You're a genius.

Many many many thanks

5

bitsmed,

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?

6

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.

7

ahhhhhh.. I see.

Thanks