How to perform a pivot on multiple columns

Question

I have a list of data that includes values and the time those values were recorded. I would like to pivot this data so that i have as columns the individual types of observations and the date/time that they were made.

Here's SQL to create some sample data:

DECLARE @Source TABLE (
  id smallint identity primary key,
  grp varchar(3) not null,
  k varchar(10) not null,
  ts datetime not null,
  val varchar(25)
);
INSERT INTO @Source (grp, k, ts, val) VALUES
('HTZ', 'color', '2014-11-19 14:22:57.633', 'Orange'),
('HTZ', 'shape', '2014-07-23 18:31:51.797', 'Cone'),
('HTZ', 'subst', '2014-01-12 04:09:15.300', 'Canvas'),
('KTU', 'color', '2014-03-08 09:16:47.450', 'Yellow'),
('KTU', 'shape', '2014-01-27 03:51:11.810', 'Octagon'),
('KTU', 'subst', '2014-06-01 20:43:22.577', 'Granite'),
('QXR', 'color', '2014-08-13 05:18:21.917', 'Pink'),
('QXR', 'shape', '2014-09-02 12:27:13.233', 'Ovoid'),
('QXR', 'subst', '2014-08-21 16:52:32.067', 'Steel'),
('WOR', 'color', '2014-09-07 01:43:55.723', 'Teal'),
('WOR', 'shape', '2014-03-14 22:04:23.680', 'Square'),
('WOR', 'subst', '2014-04-06 04:36:28.167', 'Plastic');

This is what I'm trying to achieve:

grp  color    shape    subst    color_ts                shape_ts                subst_ts
---- -------- -------- -------- ----------------------- ----------------------- -----------------------
HTZ  Orange   Cone     Canvas   2014-11-19 14:22:57.633 2014-07-23 18:31:51.797 2014-01-12 04:09:15.300
KTU  Yellow   Octagon  Granite  2014-03-08 09:16:47.450 2014-01-27 03:51:11.810 2014-06-01 20:43:22.577
QXR  Pink     Ovoid    Steel    2014-08-13 05:18:21.917 2014-09-02 12:27:13.233 2014-08-21 16:52:32.067
WOR  Teal     Square   Plastic  2014-09-07 01:43:55.723 2014-03-14 22:04:23.680 2014-04-06 04:36:28.167

I've tried the following query:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source
)
SELECT *
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

However, I end up with one row per grp+k, instead of one per grp. What am I missing?

Answer 1

So here's what I did. I put what you had in a temp table and then grouped the results like so:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select 
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

It's not pretty, but it worked.