2

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?

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.

  • I suppose that works, but why doesn't my simple query with the two pivots work? – JoeNahmias May 4 '15 at 22:42
  • It does work, it's just that you hadn't grouped by your grp column one step beyond. – SQLHound May 5 '15 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.