How to perform a pivot on multiple columns?
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?
So here's what I did to solve sql server pivot multiple columns. 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 grpIt's not pretty, but it worked.