I need the results below to "pivot" so there is a record for each _BAL column
for each dept.
==
COMPANY FISCAL_YEAR DEPT ACCOUNT SUB_ACCOUNT 00_BAL 01_BAL
100 2017 90417 5100 0 0.00 88887.85
100 2017 90417 5103 0 0.00 0.00
100 2017 90417 5200 0 0.00 173650.74
100 2017 90417 5204 0 0.00 3413.60
100 2017 90417 5210 0 0.00 315.90
100 2017 90417 5214 0 0.00 1874.20
So for dept '90417' and account '5100' the result set would look like'
COMPANY FISCAL_YEAR DEPT ACCOUNT SUB_ACCOUNT PERIOD BALANCE
100 2017 90417 5100 0 0 0.00
100 2017 90417 5100 0 1 88887.85
Below is the query that created the original results;
SELECT COMPANY,FISCAL_YEAR,ACCT_UNIT AS DEPT,ACCOUNT,SUB_ACCOUNT,DB_BEG_BAL-CR_BEG_BAL AS [00_BAL],DB_AMOUNT_01-CR_AMOUNT_01 AS [01_BAL]
FROM GLAMOUNTS
WHERE FISCAL_YEAR = '2017' AND ACCT_UNIT = '90417' AND ACCOUNT LIKE '5%'
Appreciate your suggestions.
AW
for each dept.
==
COMPANY FISCAL_YEAR DEPT ACCOUNT SUB_ACCOUNT 00_BAL 01_BAL
100 2017 90417 5100 0 0.00 88887.85
100 2017 90417 5103 0 0.00 0.00
100 2017 90417 5200 0 0.00 173650.74
100 2017 90417 5204 0 0.00 3413.60
100 2017 90417 5210 0 0.00 315.90
100 2017 90417 5214 0 0.00 1874.20
So for dept '90417' and account '5100' the result set would look like'
COMPANY FISCAL_YEAR DEPT ACCOUNT SUB_ACCOUNT PERIOD BALANCE
100 2017 90417 5100 0 0 0.00
100 2017 90417 5100 0 1 88887.85
Below is the query that created the original results;
SELECT COMPANY,FISCAL_YEAR,ACCT_UNIT AS DEPT,ACCOUNT,SUB_ACCOUNT,DB_BEG_BAL-CR_BEG_BAL AS [00_BAL],DB_AMOUNT_01-CR_AMOUNT_01 AS [01_BAL]
FROM GLAMOUNTS
WHERE FISCAL_YEAR = '2017' AND ACCT_UNIT = '90417' AND ACCOUNT LIKE '5%'
Appreciate your suggestions.
AW