SQL select on group data

Question

I have a table with the following structure

Name      Data      Month
A          100       2016-01
A          120       2016-03
A          120       2016-04
A          150       2016-05
B          100       2016-03

How can I make the final result become below between starting month and end month e.g. 2016-01 till 2016-05 using SQL if it is possible?

Name      2016-01   2016-02    2016-03     2016-04     2016-05
A          100        0          120         120        150
B           0         0          100         0           0

I did a number experiments but can't make this work. This is the code I have tried

select * 
from
(
  select id, isnull(price,0), [PERIOD]
  from price_table
) a
pivot
(
  max(price)
  for [PERIOD] in ([2010-01-01],[2010-02-01])
) p

There is error "Msg 8155, Level 16, State 2, Line 10 No column name was specified for column 2 of 'a'. Msg 207, Level 16, State 1, Line 12 Invalid column name 'price'."

Please also note that the starting months and ending months are variable in my query, the months will be dynamically added more and is not really 2016-01 till 2016-05

What SQL database are you using? MySQL? SQL Server? PostgreSQL? It will affect the answers. Also, what you're asking for specifically is how to *pivot* data i.e. rows to columns. — Jonathon Ogden, Sep 03 '17 at 16:33
Hi i am using SQL Server, I also tried to use pivot, but seem cant make it work, especially i want to make the months with "Null" Data to become 0 — onegun, Sep 03 '17 at 16:35
You can use an `isnull(, 0)` to handle that or a `CASE` statement. You'd have to apply it to each column that's pivoted. Assume you're expecting it to dynamically add more columns for each month? — Jonathon Ogden, Sep 03 '17 at 16:41
This question was answered here literally thousands of times. Just search for "sql make rows into columns" or "sql pivoting table" — Jorge Campos, Sep 03 '17 at 16:43
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Khalil, Sep 03 '17 at 17:07
Simply `select * from mytable`, maybe with some `order by` clause, and use your program / app to display the data according to your needs. Such display things should be handled in the display layer, not in the DBMS. — Thorsten Kettner, Sep 03 '17 at 19:41
Hi @JonathonOgden, the column will be dynamically added more and is not really 2016-01 till 2016-05 — onegun, Sep 03 '17 at 23:04
Please edit clarifications into your question, including tags, not into comments. — philipxy, Sep 04 '17 at 01:56

score 2 · Answer 1 · answered Sep 03 '17 at 20:18

Based on your sample data, the PIVOT query will be below

SELECT name, 
       ISNULL([2016-01],0) [2016-01], 
       ISNULL([2016-02],0) [2016-02],  
       ISNULL([2016-03],0) [2016-03], 
       ISNULL([2016-04],0) [2016-04],  
       ISNULL([2016-05],0) [2016-05]
  FROM t_data
 PIVOT (SUM(data)
        FOR [month] IN ([2016-01], [2016-02], [2016-03], [2016-04], [2016-05])
       ) p

Result

name    2016-01 2016-02 2016-03 2016-04 2016-05
A       100     0       120     120     150
B       0       0       100     0       0

I think this answer is a bit hard coding on " SELECT name, ISNULL([2016-01],0) [2016-01]" my month is variable in the database, it may not be 2016-01 — onegun, Sep 03 '17 at 22:31

SQL select on group data

1 Answers1