SQLPivot-按城鎮名稱劃分的天氣

我正在收集 3 個州的六個城鎮的天氣資料。我感興趣的主要事情是這個練習的溫度和氣壓。我想要做的是將一個基本的關系表變成一個看起來像這樣的報告：

T-SQL 表（重要）如下所示：

CREATE TABLE [dbo].[WeatherResponse](
    [WeatherResponseId] [uniqueidentifier] NOT NULL,
    [Base] [varchar](255) NULL,
    [Visibility] [int] NULL,
    [Dt] [int] NULL,
    [Timezone] [int] NULL,
    [Name] [varchar](255) NULL,
    [Id] [int] NOT NULL,
    [Created] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_WeatherResponse] PRIMARY KEY CLUSTERED 
(
    [WeatherResponseId] ASC
)

CREATE TABLE [dbo].[Mains](
    [Id] [int] NOT NULL,
    [WeatherResponseId] [uniqueidentifier] NOT NULL,
    [Temp] [float] NOT NULL,
    [FeelsLike] [float] NOT NULL,
    [TempMin] [float] NOT NULL,
    [TempMax] [float] NOT NULL,
    [Pressure] [int] NOT NULL,
    [Humidity] [int] NOT NULL,
    [SeaLevel] [int] NOT NULL,
    [GrndLevel] [int] NOT NULL,
    [Created] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Mains] PRIMARY KEY CLUSTERED 
(
    [Id] ASC,
    [WeatherResponseId] ASC
)

然而，我的支點并沒有產生預期的結果。可能是因為我在深夜作業時被新手錯誤絆倒了！;)
注意：我目前只處理 TEMP，但最終想要兩列資料。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ','   QUOTENAME([Name]) 
                    from WeatherResponse
                    group by [Name]
                    order by ','   QUOTENAME([Name])
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Created, Temp,'   @cols   ' from 
             (
                select r.Created, ((w.Temp-273.35) * (9/5))   32 as Temp, r.Name
                  from WeatherResponse r inner join Mains w on w.WeatherResponseId = r.WeatherResponseId
            ) x
            pivot 
            (
                max(Name)
                for Name in ('   @cols   ')
            ) p '

execute(@query)

結果最終看起來更像是：

那么，有人會用那個 <DOH!/> 時刻打我耳光嗎？我需要一些咖啡因。

EDIT: Added photo of results in SQL Mangler...

uj5u.com熱心網友回復：

如前所述，T-SQL 不支持pivot多個聚合函式，因此您可以對相同的功能使用條件聚合。只要您生成代碼并且不需要手動鍵入它，一個可能的缺點可能是結果查詢的大小。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

;select @cols = STUFF((
  SELECT DISTINCT
    replace(replace(replace(
      ', max(iif([Name]=val, [Temp], null)) as coltemp, max(iif([Name] = val, [Pressure], null)) as colpres
'
      /*Substitute quoted value string*/
      , 'val', quotename([Name], '''')
      )
      /*Substitute temperature column name*/
      , 'coltemp', QUOTENAME([Name]   ' temp')
      )
      /*Substitute pressure column name*/
      , 'colpres', quotename([Name]   ' pressure')
      )
  from WeatherResponse
  group by [Name]
  FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1,1,'')

;set @query = '
SELECT Created
,'   @cols   '
from (
  select
    r.Created
    , ((w.Temp-273.35) * (9/5))   32 as Temp
    , pressure
    , r.Name
  from WeatherResponse r
    inner join Mains w
      on w.WeatherResponseId = r.WeatherResponseId
) as x
group by created'

;execute( @query)
GO

已創建 | 凡士通溫度 | 凡士通壓力| Sedalia 溫度 | 塞達利亞壓力
:-------------------------- | -------------: | -----------------: | -----------: | ---------------：
2021-11-16 17:12:48.1233333 | -231.35 | 100 | -221.35 | 300

db<>在這里擺弄

你原來的問題呢：正如我所說，[Temp]列在結果集中不可用，它由pivot.

附注：請不要not null在準備最小可重現示例時在表定義中包含太多未使用的列。它們對插入一些示例資料沒有幫助，這對于動態查詢是必不可少的。

標籤：sql sql-server tsql pivot

上一篇：按過去5年過濾的SQL陳述句

下一篇：如何修復可以恢復已洗掉資料的存盤程序