0

I am attempting to show a breakdown of data like this

userid --- 2015 TA --- 2015 TI --- 2016 TA --- 2016 TI
b123 --- 26.35 --- 2 --- 36 --- 0
b143 --- 26.35 --- 2 --- 36 --- 0
b133 --- 26.35 --- 2 --- 36 --- 0

I tried this query, but it is not functioning as I thought. I thought the group by would aggregate my data as I need.

  Select 
  userid
  ,SUM(amt) As TotalAmt
  ,Sum(inflation) As TotalInflation
  FROM @information
  WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
  GROUP BY userid, d1
  ORDER BY userid ASC

How should I write the query to display data like I need? Below is garbage DDL that illustrates my data structure (of course in a real life scenario each amt would be different for each userid):

  Declare @information Table (userID varchar(25), amt float, inflation float, d1 datetime)
  Insert Into @information VALUES 
('b123', 12.23, 1.00, '01/01/2015 11:00'), 
('b123', 14.12, 1.00, '01/08/2015 11:00')
  ,('b123', 14.00, 0.00, '01/22/2016'), 
('b123', 22.00, 0.00, '02/08/2016'),
  ('b143', 12.23, 1.00, '01/01/2015 11:00'), 
('b143', 14.12, 1.00, '01/08/2015 11:00')
  ,('b143', 14.00, 0.00, '01/22/2016'), 
('b143', 22.00, 0.00, '02/08/2016'),
   ('b133', 12.23, 1.00, '01/01/2015 11:00'), 
('b133', 14.12, 1.00, '01/08/2015 11:00')
  ,('b133', 14.00, 0.00, '01/22/2016'), 
('b133', 22.00, 0.00, '02/08/2016')
2

There are a couple of ways to pivot the data to get the output you want. There's the PIVOT operator, demonstrated here: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

The syntax for PIVOT can be difficult to remember. A simpler method that usually performs just as well is to nest CASE expressions inside SUM:

SELECT userid
, SUM(CASE WHEN YEAR(d1) = 2015 THEN amt ELSE 0 END) AS [2015 TA]
, SUM(CASE WHEN YEAR(d1) = 2015 THEN inflation ELSE 0 END) AS [2015 TI]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN amt ELSE 0 END) AS [2016 TA]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN inflation ELSE 0 END) AS [2016 TI]
FROM @information
WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
GROUP BY userid
ORDER BY userid

It accomplishes the same pivot without the complexity of the PIVOT operator.

| improve this answer | |
  • I like the case option, but it looks like I am going to be stuck using a pivot() since the case adds an individual row for 2015 data and then a second for 2016 data. – SmallFries BigGuys Oct 13 '16 at 19:45
  • Whoops. The GROUP BY shouldn't have had the YEAR(d1) in there. Remove that and you'll get the expected results. I've edited the answer to show that. – Doug Lane Oct 13 '16 at 20:07

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.