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?
Thanks
1 Like
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;
1 Like
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
;
bitsmed,
IT WORKED! You're a genius.
Many many many thanks
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?
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.