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
rspencer
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
BradC
source
to share
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
Mikael Eriksson
source
to share
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
sam yi
source
to share