Konvertieren Sie mehrere Zeilen in Spalten EDIT

OK. Ich bearbeite meine Frage. Das ist mein Skript:

CREATE TABLE #Table ( Data Date, Max_Temp Real, Min_Temp Real, Sr_Temp Real, Sr_Temp_work Real) SET NOCOUNT ON DECLARE @StartTime Time DECLARE @EndTime Time DECLARE @StartTime1 Time DECLARE @EndTime1 Time DECLARE @data_start Date DECLARE @data_stop Date DECLARE @Data Date DECLARE @Data_stop_while Date DECLARE @Max_Temp Real DECLARE @Min_Temp Real DECLARE @Sr_Temp Real DECLARE @Sr_Temp_work Real SET @data_start = '20140713' SET @data_stop = '20140719' SET @StartTime = '00:00:00.000' SET @EndTime = '23:59:59.998' SET @StartTime1 = '08:00:00.000' SET @EndTime1 = '16:30:00.000' Set NOCOUNT OFF SELECT @Data = @data_start SELECT @Data_stop_while = DATEADD(day,1,@data_stop) WHILE (@Data_stop_while<>@Data) BEGIN SELECT @Max_Temp = MAX(Value), @Min_Temp = MIN(Value), @Sr_Temp = AVG(Value) FROM INSQL.Runtime.dbo.History WHERE TagName IN ('VariableName') and wwRetrievalMode = 'Cyclic' AND DateTime >= (@Data + cast(@StartTime as datetime)) AND DateTime <= (@Data + cast(@EndTime as datetime)) SELECT @Sr_Temp_work = AVG(Value) FROM INSQL.Runtime.dbo.History WHERE TagName IN ('VariableName') and wwRetrievalMode = 'Cyclic' AND DateTime >= (@Data + cast(@StartTime1 as datetime)) AND DateTime <= (@Data + cast(@EndTime1 as datetime)) INSERT INTO #Table(Data, Max_Temp, Min_Temp, Sr_Temp, Sr_Temp_work) VALUES (@Data, @Max_Temp, @Min_Temp, @Sr_Temp, @Sr_Temp_work) SELECT @Data = DATEADD(day,1,@Data) END SELECT Data, Max_Temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM #Table GO 

Wenn ich ein bestimmtes zeitintervall setze, habe ich diese tabellen und jetzt muss ich diese tabelle umwandeln:

Bildbeschreibung hier eingeben

Ich habe Pivot, unpivot und irgendwelche Links mit Foren und ich kann es nicht …

Eigentlich musst du dynamisches Pivot und unpivot zusammen verwenden. Überprüfen Sie die folgende Abfrage. Verwenden Sie den folgenden Code anstelle der letzten Auswahlabfrage in Ihrer Prozedur

 DECLARE @Columns NVARCHAR(MAX) DECLARE @MainQuery NVARCHAR(MAX) SELECT @Columns = ISNULL( @Columns + ',','') + QUOTENAME(CONVERT(VARCHAR(10),Data,20)) FROM (SELECT DISTINCT Data FROM #Table) C SET @MainQuery ='SELECT [Temperature],' + @Columns + ' FROM ( SELECT Data,Max_Temp,Min_Temp,Sr_Temp,Sr_Temp_work FROM #Table ) as T UNPIVOT ( Value FOR [Temperature] in (Max_Temp,Min_Temp,Sr_Temp,Sr_Temp_work) ) AS U PIVOT ( MAX(Value) FOR [Data] IN ('+ @Columns +') ) as P ' EXEC sp_executesql @MainQuery 

Es ist nicht bei weitem die eleganteste Lösung, aber mit einigen dynamischen SQL sollten Sie in der Lage sein, die Logik zu replizieren.

 DECLARE @temp TABLE ( Data DATE, Max_temp FLOAT, Min_Temp FLOAT, Sr_Temp FLOAT, Sr_Temp_work FLOAT ) INSERT INTO @temp select * from (values ('2014-07-13',25.8,25.6,25.701,25.668), ('2014-07-14',26.8,26.6,26.701,26.668), ('2014-07-15',27.8,27.6,27.701,27.668), ('2014-07-16',28.8,28.6,28.701,28.668))T (Data,Max_temp,Min_Temp,Sr_Temp, Sr_Temp_work) SELECT Data, Max_temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM @temp SELECT 'Max_temp' Temperature, MAX([2014-07-13])[2014-07-13], MAX([2014-07-14])[2014-07-14], MAX([2014-07-15])[2014-07-15], MAX([2014-07-16])[2014-07-16] FROM ( SELECT Data, Max_temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM @temp ) SRC PIVOT ( MAX(Max_temp) FOR Data IN ([2014-07-13],[2014-07-14],[2014-07-15],[2014-07-16]) ) AS pvt UNION ALL SELECT 'Min_Temp' Temperature, MAX([2014-07-13])[2014-07-13], MAX([2014-07-14])[2014-07-14], MAX([2014-07-15])[2014-07-15], MAX([2014-07-16])[2014-07-16] FROM ( SELECT Data, Max_temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM @temp ) SRC PIVOT ( MAX(Min_Temp) FOR Data IN ([2014-07-13],[2014-07-14],[2014-07-15],[2014-07-16]) ) AS pvt UNION ALL SELECT 'Sr_Temp' Temperature, MAX([2014-07-13])[2014-07-13], MAX([2014-07-14])[2014-07-14], MAX([2014-07-15])[2014-07-15], MAX([2014-07-16])[2014-07-16] FROM ( SELECT Data, Max_temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM @temp ) SRC PIVOT ( MAX(Sr_Temp) FOR Data IN ([2014-07-13],[2014-07-14],[2014-07-15],[2014-07-16]) ) AS pvt UNION ALL SELECT 'Sr_Temp_work' Temperature, MAX([2014-07-13])[2014-07-13], MAX([2014-07-14])[2014-07-14], MAX([2014-07-15])[2014-07-15], MAX([2014-07-16])[2014-07-16] FROM ( SELECT Data, Max_temp, Min_Temp, Sr_Temp, Sr_Temp_work FROM @temp ) SRC PIVOT ( MAX(Sr_Temp_work) FOR Data IN ([2014-07-13],[2014-07-14],[2014-07-15],[2014-07-16]) ) AS pvt 

AUF EINEM ANDEREN TON!

Verwenden Sie nicht Zeile für Zeile ..

 DECLARE @data_start Date = '2014-07-13', @data_stop Date = '2014-07-19' SELECT [DateTime] Data MAX(Value) Max_temp, MIX(Value) Min_temp, AVG(Value) Sr_Temp AVG(case when CONVERT(TIME(7),DateTime) between '08:00:00.000' and '16:30:00.000' then Value else NULL end) Sr_Temp_work FROM INSQL.Runtime.dbo.History WHERE TagName IN ('VariableName') and wwRetrievalMode = 'Cyclic' AND CONVERT(DATE, Datetime) between @data_start and @data_stop