I am collecting weather data for a half dozen towns across 3 states. The main things I am interested in is the temp and barometric pressure for this exercise. What I am trying to do is turn a basic relational table into a report that looks something like:

The T-SQL tables (of importance) look like:

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
)

My pivot, however, is NOT producing the expected results. Probably because I tripped over a rookie mistake as I work late at night on this! ;)
NOTE: I am working on just the TEMP at the moment but want both columns of data in the end.

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)

The results end up looking more like:

So, would someone slap me with that <DOH!/> moment please. I need some caffeine.

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

CodePudding user response：

As already was said, T-SQL doesn't support pivot with multiple aggregate functions, so you may use conditional aggregation for the same functionality. As long as you generate the code and you do not need to type it manually, one possible drawback may be the size of resulting query.

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

Created                     | Firestone temp | Firestone pressure | Sedalia temp | Sedalia pressure
:-------------------------- | -------------: | -----------------: | -----------: | ---------------:
2021-11-16 17:12:48.1233333 |        -231.35 |                100 |      -221.35 |              300

db<>fiddle here

What about your original issue: as I said, [Temp] column is not available in the result set, it is aggregated by pivot.

And a side note: please, do not include so much unused columns with not null in the table definition when you prepare minimal reproducible example. They doesn't help to insert some sample data, which is essential for dynamic query.