SQL Columns to rows with last 12 months of data

Question

I have a sql table with monthly consumption amounts. I would like to convert the columns to rows by cust_no and show only the last 12 months of consumption. There are many customers with years of consumption data. I would like the data to refresh as new consumption readings come in and as time goes on with the newest reading, then the one before that, and so on. What's the best way to do this?

cust_no cons read_dt
   1    1    2/28/2017 0:00
   1    2    3/31/2017 0:00
   1    2    5/1/2017 0:00
   1    2    6/1/2017 0:00
   1    2    6/30/2017 0:00
   1    2    7/31/2017 0:00
   1    2    8/29/2017 0:00
   1    3    9/29/2017 0:00
   1    2    10/31/2017 0:00
   1    2    12/1/2017 0:00
   1    2    1/2/2018 0:00
   1    3    2/1/2018 0:00
   1    2    3/2/2018 0:00
   2    1    12/21/2016 0:00
   2    0    1/20/2017 0:00
   2    0    2/21/2017 0:00
   2    1    3/24/2017 0:00
   2    1    4/24/2017 0:00
   2    2    5/25/2017 0:00
   2    2    6/26/2017 0:00
   2    1    7/25/2017 0:00
   2    1    8/24/2017 0:00
   2    2    9/25/2017 0:00
   2    2    10/26/2017 0:00
   2    2    11/27/2017 0:00
   2    1    12/26/2017 0:00
   2    2    1/26/2018 0:00
   2    2    2/26/2018 0:00

Let's check, you used correct `PIVOT` tag: **`The PIVOT syntax converts row data into columnar data, and vice versa for the UNPIVOT syntax. Not all databases support the PIVOT syntax but the functionality can be implemented, often using decision logic (CASE statements, etc) and aggregate functions. `** — Lukasz Szozda, Mar 20 '18 at 20:19
Possible duplicate of [PIVOT with MONTH()](https://stackoverflow.com/questions/7145694/pivot-with-month) — S3S, Mar 20 '18 at 20:20
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Mar 20 '18 at 20:21
@dfundako, you couldn't have downvoted, because there's only one DV so far, and I know it's mine. — Tab Alleman, Mar 20 '18 at 20:22
With respect to you all, it isn't necessarily clear to SQL beginners that this operation is called a *pivot*. — O. Jones, Mar 20 '18 at 20:23
@O.Jones The OP tagged the post with PIVOT. Now, he/she should have put that into a Google search along the lines of 'SQL Server PIVOT' and there would have been a cornucopia of results to get started on. — dfundako, Mar 20 '18 at 20:26
What is your metric for _best_? Least work on your part? Fewest characters when expressed TSQL? Smallest memory footprint? Most effective at parallel execution? — HABO, Mar 20 '18 at 22:06
Thanks for pointing me to PIVOT. I've edited my question to hopefully explain that I would like to have the most recent consumption in the 3rd column, the next most recent in the 4th column and so on. — Josh, Mar 21 '18 at 12:39

score 3 · Answer 1 · answered Mar 20 '18 at 20:27

3

try this:

   select * from    (select * from #temp
   where read_dt between dateadd(yy,-1, read_dt )and getdate() )a 
   pivot (max (read_dt) for
   cons in  ([1],[2],[3]))b

answered Mar 20 '18 at 20:27

Daniel Marcus

2,686
1
7
13

Thanks! That kind of gets me there. I'm still seeing multiple rows for the same customer, though. Also, I had to change dateadd(yy,-1, read_dt ) to dateadd(yy,-1, getdate() ) to get some consumption numbers from the last year. I'm not sure why I'm not getting all of them. `select * from (select * from complus90.dbo.cubconsump where read_dt between dateadd(mm,-12, getdate() )and getdate() )a pivot (max (read_dt) for cons in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))b` – Josh Mar 21 '18 at 12:41
Can you give me an example of the duplicate rows you are seeing? – Daniel Marcus Mar 21 '18 at 15:30
Not seeing duplicate rows now, but am seeing date values instead of consumption values. – Josh Mar 22 '18 at 12:52
ok so just switch the pivot field like this (fill in dates in table where I put 'date1', 'date2', etc): select * from (select * from complus90.dbo.cubconsump where read_dt between dateadd(mm,-12, getdate() )and getdate() )a pivot (max (cons) for read_dt in ([date1], [date2],...))b – Daniel Marcus Mar 22 '18 at 13:47
It appears that the date values are going into the cells where the cons = the column header. – Josh Mar 22 '18 at 15:09

SQL Columns to rows with last 12 months of data

1 Answers1