Pivot SQL possible pour cet set de données?

J'essaie d'écrire une requête et j'ai besoin d'aide.

J'ai des données qui ressemblent à ceci:

J'ai des données qui ressemblent à ceci

Cependant, les users finaux aimeraient un Pivot pour que datatables s'affichent comme ceci:

entrez la description de l'image ici

J'ai écrit ce code:

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;

Mais cela revient:

entrez la description de l'image ici

Mes questions sont:

1) Pourquoi mon Pivot ne fonctionne-t-il pas? 2) Est-il même possible de faire ce dont j'ai besoin avec un Pivot?

Merci pour toute aide.

Par request, voici datatables:

 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

Voici l'set de résultats:

 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

Voici comment vous pouvez tirer parti d'un tableau croisé dynamic pour accomplir ce type de chose. Jeff Moden a un article fantastique expliquant cette technique ici. 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 tN <= ( 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

Une autre option pour un pivot dynamic

La torsion était les colonnes non distinctes / répétitives dans les résultats finaux. Comme vous pouvez le voir, cela a été résolu avec un simple rlocation (x3).

 Declare @SQL varchar(max) = Stuff((Select Distinct ','+QuoteName(concat('A',ColGrp)) +','+QuoteName(concat('B',ColGrp)) +','+QuoteName(concat('C',ColGrp)) From (Select Distinct ColGrp=Row_Number() over (Partition By sn,Instant Order by [Address/Key]) From YourTable ) A Order By 1 For XML Path('')),1,1,'') Select @SQL = ' Select [SN],[Instant],' + replace(replace(replace(@SQL,'[A','[Address/Key]=[A'),'[C','[Actual]=[C'),'[B','[Command]=[B') + ' From ( Select [SN] ,[Instant] ,[Col] = Concat(B.Prefix,RN ) ,B.Value From (Select *,RN=Row_Number() over (Partition By sn,Instant Order by [Address/Key]) From YourTable) A Cross Apply (values (''A'',[Address/Key]) ,(''B'',cast(Command as varchar(25))) ,(''C'',cast(Actual as varchar(25))) ) B (PreFix,Value) ) A Pivot (max([Value]) For [Col] in (' + @SQL + ') ) p' --Print @SQL Exec(@SQL);

Résultats

entrez la description de l'image ici

EDIT – Si cela aide avec la visualisation

Le SQL généré ressemble à ceci

 Select [SN],[Instant],[Address/Key]=[A1],[Command]=[B1],[Actual]=[C1],[Address/Key]=[A2],[Command]=[B2],[Actual]=[C2],[Address/Key]=[A3],[Command]=[B3],[Actual]=[C3],[Address/Key]=[A4],[Command]=[B4],[Actual]=[C4],[Address/Key]=[A5],[Command]=[B5],[Actual]=[C5],[Address/Key]=[A6],[Command]=[B6],[Actual]=[C6] From ( Select [SN] ,[Instant] ,[Col] = Concat(B.Prefix,RN ) ,B.Value From (Select *,RN=Row_Number() over (Partition By sn,Instant Order by [Address/Key]) From YourTable) A Cross Apply (values ('A',[Address/Key]) ,('B',cast(Command as varchar(25))) ,('C',cast(Actual as varchar(25))) ) B (PreFix,Value) ) A Pivot (max([Value]) For [Col] in ([A1],[B1],[C1],[A2],[B2],[C2],[A3],[B3],[C3],[A4],[B4],[C4],[A5],[B5],[C5],[A6],[B6],[C6]) ) p