July 7, 2017 at 2:22 pm
I have been agonizing over this for many hours and have decided to ask for help. I have only done one other pivot and it was simpler than this. I am not a DBA or a programmer - more a business analyst type with some SQL skills.
What I am trying to do is join data from two table - incident and source and summarize the data since January 2014 by quarter. Since we just finished 2017 - Q2, that would be 14 columns. I know I will have to add another column each quarter.
My expected results are:
| ClientName | 2014 - Q1 | 2014 - Q2 | 2014 - Q3 | 2014 - Q4 | 2015 - Q1 | 2015 - Q2 | 2015 - Q3 | 2015 - Q4 | 2016 - Q1 | 2016 - Q2 | 2016 - Q3 | 2016 - Q4 | 2017 - Q1 | 2017 - Q2 |
| ANZ | 8 | 17 | 10 | 4 | 8 | 13 | 19 | 10 | 3 | NULL | 1 | NULL | NULL | NULL |
My actual results are:
| ClientName | 2014 - Q1 | 2014 - Q2 | 2014 - Q3 | 2014 - Q4 | 2015 - Q1 | 2015 - Q2 | 2015 - Q3 | 2015 - Q4 | 2016 - Q1 | 2016 - Q2 | 2016 - Q3 | 2016 - Q4 | 2017 - Q1 | 2017 - Q2 |
| ANZ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
My SQL does compile, but it returns NULL for each column.
My SQL to create the tables and populate them:
CREATE TABLE incident
(
incidentno nvarchar(255),
sourceid nvarchar(255),
opened datetime
);
CREATE TABLE source
(
s_id nvarchar(255),
S_name nvarchar(255)
);
INSERT INTO source VALUES
('OP_Pac_ANZ', 'ANZ');
INSERT INTO incident
(incidentno, sourceid, opened)
VALUES
('1506110002', 'OP_Pac_ANZ', '2015-06-11 02:36:00.000'),
('1506110100', 'OP_Pac_ANZ', '2015-06-11 21:48:00.000'),
('1506120010', 'OP_Pac_ANZ', '2015-06-12 03:00:00.000'),
('1501260175', 'OP_Pac_ANZ', '2015-01-26 23:34:00.000'),
('1501290278', 'OP_Pac_ANZ', '2015-01-29 19:40:00.000'),
('1502110143', 'OP_Pac_ANZ', '2015-02-11 15:28:00.000'),
('1501090237', 'OP_Pac_ANZ', '2015-01-09 16:52:00.000'),
('1506150148', 'OP_Pac_ANZ', '2015-06-15 23:31:00.000'),
('1506160142', 'OP_Pac_ANZ', '2015-06-16 23:19:00.000'),
('1506170005', 'OP_Pac_ANZ', '2015-06-17 04:10:00.000'),
('1506170113', 'OP_Pac_ANZ', '2015-06-17 20:53:00.000'),
('1507010002', 'OP_Pac_ANZ', '2015-07-01 00:49:00.000'),
('1507010006', 'OP_Pac_ANZ', '2015-07-01 03:36:00.000'),
('1507010012', 'OP_Pac_ANZ', '2015-07-01 05:06:00.000'),
('1507020104', 'OP_Pac_ANZ', '2015-07-02 22:21:00.000'),
('1507030002', 'OP_Pac_ANZ', '2015-07-03 03:01:00.000'),
('1507030003', 'OP_Pac_ANZ', '2015-07-03 03:11:00.000'),
('1507080007', 'OP_Pac_ANZ', '2015-07-08 05:10:00.000'),
('1507080039', 'OP_Pac_ANZ', '2015-07-08 11:11:00.000'),
('1507080124', 'OP_Pac_ANZ', '2015-07-08 23:31:00.000'),
('1507100003', 'OP_Pac_ANZ', '2015-07-10 01:39:00.000'),
('1507120001', 'OP_Pac_ANZ', '2015-07-12 00:02:00.000'),
('1503300232', 'OP_Pac_ANZ', '2015-03-30 21:37:00.000'),
('1405280003', 'OP_Pac_ANZ', '2014-05-28 04:43:00.000'),
('1405280325', 'OP_Pac_ANZ', '2014-05-28 22:22:00.000'),
('1405290007', 'OP_Pac_ANZ', '2014-05-29 04:11:00.000'),
('1405290009', 'OP_Pac_ANZ', '2014-05-29 04:23:00.000'),
('1406030002', 'OP_Pac_ANZ', '2014-06-03 02:07:00.000'),
('1405140050', 'OP_Pac_ANZ', '2014-05-14 08:47:00.000'),
('1405140322', 'OP_Pac_ANZ', '2014-05-14 19:22:00.000'),
('1405140327', 'OP_Pac_ANZ', '2014-05-14 19:42:00.000'),
('1501120369', 'OP_Pac_ANZ', '2015-01-12 19:36:00.000'),
('1504200174', 'OP_Pac_ANZ', '2015-04-20 23:42:00.000'),
('1510080001', 'OP_Pac_ANZ', '2015-10-08 00:59:00.000'),
('1510080128', 'OP_Pac_ANZ', '2015-10-08 16:39:00.000'),
('1510120006', 'OP_Pac_ANZ', '2015-10-12 03:49:00.000'),
('1510120111', 'OP_Pac_ANZ', '2015-10-12 22:32:00.000'),
('1510120114', 'OP_Pac_ANZ', '2015-10-12 23:33:00.000'),
('1510130002', 'OP_Pac_ANZ', '2015-10-13 03:35:00.000'),
('1503040057', 'OP_Pac_ANZ', '2015-03-04 11:10:00.000'),
('1503300005', 'OP_Pac_ANZ', '2015-03-30 02:39:00.000'),
('1403110339', 'OP_Pac_ANZ', '2014-03-11 21:52:00.000'),
('1406150021', 'OP_Pac_ANZ', '2014-06-15 21:18:00.000'),
('1408130367', 'OP_Pac_ANZ', '2014-08-13 22:13:00.000'),
('1506190003', 'OP_Pac_ANZ', '2015-06-19 00:18:00.000'),
('1406290015', 'OP_Pac_ANZ', '2014-06-29 19:59:00.000'),
('1406290016', 'OP_Pac_ANZ', '2014-06-29 20:03:00.000'),
('1410120030', 'OP_Pac_ANZ', '2014-10-12 21:20:00.000'),
('1408010001', 'OP_Pac_ANZ', '2014-08-01 03:13:00.000'),
('1408030020', 'OP_Pac_ANZ', '2014-08-03 23:38:00.000'),
('1408060301', 'OP_Pac_ANZ', '2014-08-06 22:08:00.000'),
('1403180346', 'OP_Pac_ANZ', '2014-03-18 21:29:00.000'),
('1405150357', 'OP_Pac_ANZ', '2014-05-15 22:42:00.000'),
('1405180033', 'OP_Pac_ANZ', '2014-05-18 20:24:00.000'),
('1405190025', 'OP_Pac_ANZ', '2014-05-19 07:31:00.000'),
('1402170070', 'OP_Pac_ANZ', '2014-02-17 11:07:00.000'),
('1407080003', 'OP_Pac_ANZ', '2014-07-08 03:27:00.000'),
('1407130019', 'OP_Pac_ANZ', '2014-07-13 22:26:00.000'),
('1408190333', 'OP_Pac_ANZ', '2014-08-19 21:18:00.000'),
('1410150001', 'OP_Pac_ANZ', '2014-10-15 01:12:00.000'),
('1402240330', 'OP_Pac_ANZ', '2014-02-24 18:40:00.000'),
('1512210004', 'OP_Pac_ANZ', '2015-12-21 01:30:00.000'),
('1512210005', 'OP_Pac_ANZ', '2015-12-21 01:36:00.000'),
('1403310361', 'OP_Pac_ANZ', '2014-03-31 21:21:00.000'),
('1403030003', 'OP_Pac_ANZ', '2014-03-03 02:46:00.000'),
('1403030417', 'OP_Pac_ANZ', '2014-03-03 19:40:00.000'),
('1410290005', 'OP_Pac_ANZ', '2014-10-29 00:38:00.000'),
('1412220320', 'OP_Pac_ANZ', '2014-12-22 21:10:00.000'),
('1506260012', 'OP_Pac_ANZ', '2015-06-26 03:50:00.000'),
('1506290005', 'OP_Pac_ANZ', '2015-06-29 03:11:00.000'),
('1506300145', 'OP_Pac_ANZ', '2015-06-30 21:06:00.000'),
('1506300150', 'OP_Pac_ANZ', '2015-06-30 23:09:00.000'),
('1406180002', 'OP_Pac_ANZ', '2014-06-18 00:43:00.000'),
('1406180003', 'OP_Pac_ANZ', '2014-06-18 00:45:00.000'),
('1406220007', 'OP_Pac_ANZ', '2014-06-22 09:24:00.000'),
('1408280001', 'OP_Pac_ANZ', '2014-08-28 02:13:00.000'),
('1409070033', 'OP_Pac_ANZ', '2014-09-07 20:28:00.000'),
('1409080003', 'OP_Pac_ANZ', '2014-09-08 01:12:00.000'),
('1508100003', 'OP_Pac_ANZ', '2015-08-10 04:10:00.000'),
('1508110004', 'OP_Pac_ANZ', '2015-08-11 02:54:00.000'),
('1508130106', 'OP_Pac_ANZ', '2015-08-13 14:26:00.000'),
('1509300036', 'OP_Pac_ANZ', '2015-09-30 05:08:00.000'),
('1508190197', 'OP_Pac_ANZ', '2015-08-19 20:24:00.000'),
('1509230005', 'OP_Pac_ANZ', '2015-09-23 01:18:00.000'),
('1509230194', 'OP_Pac_ANZ', '2015-09-23 21:41:00.000'),
('1511230002', 'OP_Pac_ANZ', '2015-11-23 04:13:00.000'),
('1511240121', 'OP_Pac_ANZ', '2015-11-24 21:05:00.000'),
('1601240046', 'OP_Pac_ANZ', '2016-01-24 21:41:00.000'),
('1603200028', 'OP_Pac_ANZ', '2016-03-20 22:46:00.000'),
('1602010003', 'OP_Pac_ANZ', '2016-02-01 03:35:00.000'),
('1608090001', 'OP_Pac_ANZ', '2016-08-09 00:49:00.000');
My query that returns all NULL values:
SELECT [ClientName],
['20141'] AS [2014 - Q1],
['20142'] AS [2014 - Q2],
['20143'] AS [2014 - Q3],
['20144'] AS [2014 - Q4],
['20151'] AS [2015 - Q1],
['20152'] AS [2015 - Q2],
['20153'] AS [2015 - Q3],
['20154'] AS [2015 - Q4],
['20161'] AS [2016 - Q1],
['20161'] AS [2016 - Q2],
['20162'] AS [2016 - Q3],
['20163'] AS [2016 - Q4],
['20171'] AS [2017 - Q1]
FROM(
SELECT COALESCE(S.S_Name,'') AS [ClientName],
(CAST(DATEPART(YEAR,I.opened) AS VARCHAR(4)) + CAST(DATEPART(QUARTER,I.opened) AS VARCHAR(1))) AS [Period],
COUNT(I.incidentno) AS [IncidentCount]
FROM incident I
LEFT OUTER JOIN source S ON I.SourceId = S.S_ID
WHERE I.SourceId = 'OP_Pac_ANZ'
GROUP BY COALESCE(S.S_Name,''), (CAST(DATEPART(YEAR,I.opened) AS VARCHAR(4)) + CAST(DATEPART(QUARTER,I.opened) AS VARCHAR(1)))
)
src
PIVOT (SUM(src.IncidentCount)
FOR [Period] IN (['20141'],['20142'],['20143'],['20144'],['20151'],['20152'],['20153'],['20154'],['20161'],['20162'],['20163'],['20164'],['20171'])) AS pvt
What am I doing wrong? There are other things I would like to do with pivot tables and I feel if I can understand this, I would be on my way.
Thanks so much for any help!
July 7, 2017 at 2:51 pm
Get rid of all the single quotes in your pivoted column references (both in the IN and in the top-level SELECT).
Cheers!
July 7, 2017 at 6:01 pm
Thanks Jacob! I am banging my head on my desk now! To be soooo close and have the singles quotes be the error!
I fixed my SQL and I saw another error, which I fixed and now it runs as expected. I had erroneously selected two 20161 quarters!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply