此数据集可能使用SQL Pivot吗? [英] SQL Pivot possible for this data set?

问题描述

我正在尝试编写查询，我需要一些帮助.

I trying to write a query and I need some help.

我有一些看起来像这样的数据:

I have some data that looks like this:

但是，最终用户希望使用数据透视表，以便数据显示如下:

However, the end users would like a Pivot so that the data displays like this:

我写了这段代码:

SELECT *
FROM
(
SELECT TOP 100 
Log0.sn
,CONVERT(VARCHAR(50), Log0.capture_time) AS Instant
,CONVERT(VARCHAR(50),Log0.address) + '/' + Log0.[Key] AS 'Address/Key'
,Log0.average AS Command
,Log1.average AS Actual

FROM ih3_fan_speed_log_0 Log0
LEFT JOIN ih3_fan_speed_log_01 Log1 ON Log0.sn = Log1.sn AND 
Log0.capture_time = Log1.capture_time AND Log0.address = Log1.address

WHERE Log0.sn = 'V300188' AND Log0.capture_time = '03/26/2017 13:05:00'
) Src

pivot
(
  MAX(sn)
  for Instant in ([1], [2], [3], [4])
) piv;

但是返回:

我的问题是:

1)为什么我的Pivot不起作用? 2)甚至可以用Pivot做我需要的事情吗?

1) Why doesn't my Pivot work ? 2) Is it even possible to do what I need with a Pivot ?

感谢您的帮助.

每个请求的数据如下:

sn  Instant Address/Key Command Actual
V300188     Mar 26 2017  1:05PM 1/EF-1  99.23   99.24
V300188     Mar 26 2017  1:05PM 2/EF-2  99.02   99.04
V300188     Mar 26 2017  1:05PM 3/EF-3  100 0
V300188     Mar 26 2017  1:05PM 4/EF-4  100 100
V300188     Mar 26 2017  1:05PM 41/MUA-1    74.58   74.58
V300188     Mar 26 2017  1:05PM 51/0-10VDC  74.58   74.58

这是结果集:

Address/Key Command Actual  1   2   3   4
1/EF-1  99.23   99.24   NULL    NULL    NULL    NULL
2/EF-2  99.02   99.04   NULL    NULL    NULL    NULL
3/EF-3  100 0   NULL    NULL    NULL    NULL
4/EF-4  100 100 NULL    NULL    NULL    NULL
41/MUA-1    74.58   74.58   NULL    NULL    NULL    NULL
51/0-10VDC  74.58   74.58   NULL    NULL    NULL    NULL

推荐答案

在这里，您可以利用动态交叉表来完成这种事情.杰夫·摩登(Jeff Moden)的精彩文章在这里解释了这种技术. http://www.sqlservercentral.com/articles/Crosstab/65048/

Here is how you can leverage a dynamic crosstab to accomplish this type of thing. Jeff Moden has a fantastic article explaining this technique here. http://www.sqlservercentral.com/articles/Crosstab/65048/

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something

create table #Something
(
    sn varchar(10)
    , Instant datetime
    , [Address/Key] varchar(20)
    , Command numeric(7,2)
    , Actual numeric(7,2)
)

insert #Something
select 'V300188', 'Mar 26 2017  1:05PM', '1/EF-1', 99.23, 99.24 union all
select 'V300188', 'Mar 26 2017  1:05PM', '2/EF-2',  99.02, 99.04 union all
select 'V300188', 'Mar 26 2017  1:05PM', '3/EF-3', 100, 0 union all
select 'V300188', 'Mar 26 2017  1:05PM', '4/EF-4', 100, 100 union all
select 'V300188', 'Mar 26 2017  1:05PM', '41/MUA-1', 74.58, 74.58 union all
select 'V300188', 'Mar 26 2017  1:05PM', '51/0-10VDC', 74.58,74.58

declare @MaxCols int

declare @StaticPortion nvarchar(2000) = 
    'with OrderedResults as
    (
        select *, ROW_NUMBER() over(partition by sn order by instant) as RowNum
        from #Something
    )
    select sn, instant';

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by sn, instant order by sn';

with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS 
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)

select @DynamicPortion = @DynamicPortion + 
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then [Address/Key] end) as address' + CAST(N as varchar(6)) + CHAR(10) +
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Command end) as Command' + CAST(N as varchar(6)) + CHAR(10) +
    ', MAX(Case when RowNum = ' + CAST(N as varchar(6)) + ' then Actual end) as Actual' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= 
(
    select top 1 Count(*)
    from #Something
    group by sn, instant
    order by COUNT(*) desc
)

select @StaticPortion + @DynamicPortion + @FinalStaticPortion

declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
exec sp_executesql @SqlToExecute

这篇关于此数据集可能使用SQL Pivot吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！