Clever Geek Handbook

Retrieving Query Data to Create a New Table

I have a table I run a query and get results like:

-----------------------------------------------------------------------------------------------
Account January February March April May June July August September October November December
-----------------------------------------------------------------------------------------------
1234      0        50     100    0    0    0   120   280    800        0      650      124
-----------------------------------------------------------------------------------------------

How can I transform this query to display data such as:

------------------------
Account    Period  Amount
-------------------------
1234        Jan      0
1234        Feb      50
1234        Mar      100
.
.
1234        Dec      124
--------------------------

Would I create a view, store a proc, or something else? (sorry I don't know as I'm not a DBA, but you need to find a solution to help with this.)

+3


source to share


3 answers


You can display it with a query like this:



SELECT Account, 'Jan' as Period, January as Amount
FROM myTable

UNION ALL

SELECT Account, 'Feb' as Period, February as Amount
FROM myTable

UNION ALL

...
+1


source


You can use univot if supported by your database management system.

Something like that:

select U.Account, 
       U.Period,
       U.Amount
from 
  ( -- Your query goes here
    select 
      1234 as Account,
      0 as Januar,
      50 as February,
      100 as March
  ) as T
unpivot
  (
    Amount for Period in (Januar, February, March)
  ) as U


Result:

Account     Period     Amount
----------- ---------- -----------
1234        Januar     0
1234        February   50
1234        March      100
+1


source


I think the above univot answer is probably better if your db supports it, but ... I have a feeling that your original request could be modified by doing this with a simple group "

select ..
from <table>
where Account = '1234'
group by Account, Period
0


source






More articles:


All Articles