Pivot using Multiple columns in SQL

Question

I need to pivot a table as show below using column "channel" and grouping it based on Units.

Actual table:

The result I need is shown below

I'm not an expert with pivotting and unpivoting concepts, I'm trying the below query to achieve the above result

SELECT [service_point_ID]
           ,isnull([1],0) - isnull([2],0) as net_usage_value
           ,[units]
            ,[1]
            ,[2]
            ,[channel_ID]
            ,[date]
            ,[time]
            ,[is_estimate]
            ,[UTC_offset]
            ,[import_history_id]                     
       FROM #temp1
       AS SourceTable PIVOT(sum(usage_value) FOR channel IN([1],[2])) AS PivotTable

If I execute this query I'm getting the below result

The same logic is achieved in r -Refernce link Pivot using Mutiple columns

Here is the SQL fiddle for this one

CREATE TABLE #temp1
(
 Service_point_ID varchar(10) NUll,
 usage_value decimal(18,6) NULL,
 units varchar(10) NUll,
 [date] Date NULL,
 [time] time NULL,
 channel varchar(2) NULL,
 [Channel_ID] varchar(2) NULL,
 is_estimate varchar(2) NULL,
 UTC_Offset varchar(20) NULL
)

INSERT INTO #temp1 VALUES ('123',1.000000,'kvarh','2017-01-01','0015','1','11','A','-500')
INSERT INTO #temp1 VALUES ('123',0.200000,'kvarh','2017-01-01','0015','2','11','A','-500')
INSERT INTO #temp1 VALUES ('123',0.200000,'kwh','2017-01-01','0015','1','11','A','-500')
INSERT INTO #temp1 VALUES ('123',0.400000,'kwh','2017-01-01','0015','2','11','A','-500')

Any help is much appreciated.

You should give us some script generating temporary table with your data or SQL fiddle, so we can try help you. Pasting images is discouraged. — Michał Turczyn, Mar 28 '18 at 15:52
Pivot isn't what you're trying to do here...you just need a group by statement to bring these to one line — Twelfth, Mar 28 '18 at 15:56
Yes but group by reduces the performance, besides as you can see I need to calculated the net usage value based on channel 1 and 2 — Gowtham Ramamoorthy, Mar 28 '18 at 15:58

score 2 · Answer 1 · answered Mar 28 '18 at 16:09

This is solution using pivot function:

declare @table table(
    service_point_id int,
    usage_value float,
    units varchar(10),
    [date] date,
    [time] char(4),
    channel int,
    channel_id int,
    is_estimate char(1),
    utc_offset int,
    import_history int,
    datecreated datetime
)
--example data you provided
insert into @table values
(123, 1, 'kvarh', '2017-01-01', '0015', 1, 11, 'A', -500, 317, '2018-03-20 10:32:42.817'),
(123, 0.2, 'kwh', '2017-01-01', '0015', 1, 33, 'A', -500, 317, '2018-03-20 10:32:42.817'),
(123, 0.3, 'kvarh', '2017-01-01', '0015', 2, 11, 'A', -500, 317, '2018-03-20 10:32:42.817'),
(123, 0.4, 'kwh', '2017-01-01', '0015', 2, 33, 'A', -500, 317, '2018-03-20 10:32:42.817')

--pivot query that does the work, it's only matter of aggregation one column, as mentioned already, so pivot query is really simple and concise
select *, [1]-[2] [net_usage_value] from 
(select * from @table) [t]
pivot (
max(usage_value)
for channel in ([1],[2])
) [a]

score 1 · Accepted Answer · answered Mar 28 '18 at 15:57

1

SELECT [service_point_ID]
           sum(,isnull([1],0) - isnull([2],0)) as net_usage_value
           ,[units]
            ,sum(isnull([1],0))[1]
            ,sum(isnull([2],0))[2]
            ,[channel_ID]
            ,[date]
            ,[time]
            ,[is_estimate]
            ,[UTC_offset]
            ,[import_history_id]                     
       FROM #temp1
       AS SourceTable PIVOT(sum(usage_value) FOR channel IN([1],[2])) AS PivotTable
group by [service_point_ID], [units],[channel_ID]
            ,[date]
            ,[time]
            ,[is_estimate]
            ,[UTC_offset]
            ,[import_history_id]

answered Mar 28 '18 at 15:57

Daniel Marcus

2,686
1
7
13

Dude you are awesome...!!! quick question is this achievable without using group by ? Goal is to get the better performance FYI- we can add intermediary tables to achieve better performance) – Gowtham Ramamoorthy Mar 28 '18 at 16:01
Thanks! Don't think you can get rid of group by completely but try to group by first , create a new temp table with the results, and then pivot off the new temp table - might be faster – Daniel Marcus Mar 28 '18 at 16:15

score 0 · Answer 3 · answered Mar 28 '18 at 17:17

Inner join will out perform the pivot syntax. SQL Server pivot vs. multiple join

select a.usage_value - b.usage_value as net_usage_value , other columns
from #temp1 a inner join #temp1 b on a.service_point_id = b.service_point_id 
and a.units = b.units 
and a.channel = 1 
and b.channel = 2

gets around the group by as well.

Pivot using Multiple columns in SQL

3 Answers3