How to add corresponding columns to a PIVOT dataset

Question

Using Microsoft SQL Server 2012.

I have a table:

PartID |TypeSet | Setting | ObservID |TransDate
---------------------------------------------
 1         A    456       12    10/20/2015
 1         A    377       12    10/20/2015
 1         A    425       12    10/20/2015
 1         A    665       12    10/20/2015
 1         A    543       12    10/20/2015
 1         A    554       12    10/20/2015
 1         B    34        41    10/21/2015
 1         B    27        41    10/21/2015
 1         B    27        41    10/21/2015
 1         B    29        41    10/21/2015
 1         C    1299      12    10/20/2015
 1         C    1227      12    10/20/2015

I would like to display it as the following. Single row grouped on PartID.

PartID | A Avg | B Avg | C Avg |  A ObservID | B ObservID | C ObservID
 ---------------------------------------------------------------------
  1      503      29     1263        12             41         12

I cannot get the ObservIDs to display w/o creating a multi-row return dataset. I have no problem with the A, B and C Average with this query:

  SELECT    
      PartID
      ,[A] as 'A Average'
      ,[B] as 'B Average'   
      ,[C] as 'C Average'
     -- ,(ObservID)
     --,CASE WHEN [A] = 'A Average' THEN max([ObservID]) END as 'A Ob'
     --,CASE WHEN [B] = 'B Average' THEN max([ObservID]) END as 'B Ob'
     -- ,CASE WHEN [C] = 'C Average' THEN max([ObservID]) END as 'C Ob'
FROM
(SELECT
    PartID  
    ,TypeSet
    ,Setting
    --,ObservID
    FROM  #Temp1
    ) as MeasData
PIVOT
(
    AVG(Setting) 
    FOR [TypeSet] in 
        ( [A], [B], [C])    
) as PivotTable

You can see in the above query where I've tried to get ObservID in it but when I uncomment those lines, errors occur. Some notes: The ObservIDs will stay consistent for each TypeSet.

Here's the data:

create table #Temp1
(
  PartID INT
, TypeSet VARCHAR(10) 
, Setting INT
, ObservID INT
, TransDate Date
)

INSERT INTO #Temp1(PartID, TypeSet, Setting, ObservID, TransDate) VALUES
  (1, 'A', 456, 12, '10/20/2015'),
  (1, 'A', 377, 12, '10/20/2015'),
  (1, 'A', 425, 12, '10/20/2015'),
  (1, 'A', 665, 12, '10/20/2015'),
  (1, 'A', 543, 12, '10/20/2015'),
  (1, 'A', 554, 12, '10/20/2015'),
  (1, 'B', 34, 41, '10/21/2015'),
  (1, 'B', 27, 41, '10/21/2015'),
  (1, 'B', 27, 41, '10/21/2015'),
  (1, 'B', 29, 41, '10/21/2015'),
  (1, 'C', 1299, 12, '10/20/2015'),
  (1, 'C', 1227, 12, '10/20/2015')

Is my desired output possible with the PIVOT operation? Or should I go the route of multiple JOINs on the table for each Typeset?

Answer 1

You can do it using conditional aggregation:

SELECT PartID,
     AVG(CASE WHEN TypeSet = 'A' THEN Setting END) AS 'A Avg',
     AVG(CASE WHEN TypeSet = 'B' THEN Setting END) AS 'B Avg',
     AVG(CASE WHEN TypeSet = 'C' THEN Setting END) AS 'C Avg',
     MAX(CASE WHEN TypeSet = 'A' THEN ObservID END) AS 'A ObservID',
     MAX(CASE WHEN TypeSet = 'B' THEN ObservID END) AS 'B ObservID',
     MAX(CASE WHEN TypeSet = 'C' THEN ObservID END) AS 'C ObservID'
FROM #Temp1
GROUP BY PartID