代码之家 › 专栏 › 技术社区 › Gowtham Ramamoorthy

在SQL中使用多列透视

pivot sql-server-2012 tsql sql-server sql

-1

Gowtham Ramamoorthy · 技术社区 · 7 年前

我需要使用列“channel”透视一个如下所示的表,并根据单位对其进行分组。

实际表格:

我需要的结果如下所示

我不是数据透视和取消数据透视概念的专家,我正在尝试下面的查询以获得上述结果

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

如果执行此查询,将得到以下结果

在r-Reference链接中实现了相同的逻辑 Pivot using Mutiple columns

下面是这一个的SQL小提琴

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')

非常感谢您的帮助。

3 回复 | 直到 7 年前

MichaÅ Turczyn 7 年前

这是使用pivot函数的解决方案:

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]

Daniel Marcus 7 年前

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]

Twelfth 7 年前

内部联接将执行pivot语法。 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

也可以绕过小组。