CODEWITHSUNDEEP
sqlsql-server-2012
Niron11
Niron11

Reputation: 13

SQL query and troubleshooting

Good day to you all, I have a question regarding my sql query on rearranging the data. the query result look like this.

Area | Name | Amount |   Date
  1  |  N1  |   10   | 6/15/2019
  2  |  N1  |   20   | 6/15/2019
  3  |  N1  |   30   | 6/15/2019
  4  |  N1  |   77   | 6/15/2019
  1  |  N2  |   30   | 6/15/2019
  2  |  N2  |   45   | 6/15/2019
  3  |  N2  |   60   | 6/15/2019

Is It possible to make the query result into something like this and how do you achieve it?

Area | Name | Amount | Name | Amount
  1  |  N1  |   10   |  N2  |   30
  2  |  N1  |   20   |  N2  |   45
  3  |  N1  |   30   |  N2  |   60
  4  |  N1  |   77   | Null |  Null

The problem is the user doesn't know the names because it is generated depending on the date it is inserted.

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If I understand correctly, then conditional aggregation would work:

select area,
       max(case when seqnum = 1 then name end) as name1,
       max(case when seqnum = 1 then amount end) as amount1,
       max(case when seqnum = 2 then name end) as name2,
       max(case when seqnum = 2 then amount end) as amount2
from (select t.*,
             row_number() over (partition by area order by name) as seqnum
      from t
     ) t
group by area;

Upvotes: 1

Related Questions