大家好
小弟有一個表格如下：
Years Groups Balance
2017 1-Net Sales 4035508.167
2017 2-COGS -2282458
2017 3-Operating Gross Profit -73739.13333
2017 4-Operating Net Profit -1475871.451
2017 5-Profit and Loss 7867576.751
2018 1-Net Sales 5716035.333
2018 2-COGS -2784823.8
2018 3-Operating Gross Profit -533335.6667
2018 4-Operating Net Profit -1527671.1
2018 5-Profit and Loss 10561865.9
小弟想把格式轉為
Groups Balance-2017 Balance-2018
1-Net Sales 4035508.167 5716035.333
2-COGS -2282458 -2784823.8
3-Operating Gross Profit -73739.13333 -533335.6667
4-Operating Net Profit -1475871.451 -1527671.1
5-Profit and Loss 7867576.751 10561865.9
原以為很簡單的用子查詢就可做到，沒想到試了很久都一直出現錯誤訊息，不得其法，煩請知道如何處理的網大協助。
謝謝!!
已邀請的邦友 {{ invite_list.length }}/5
你沒畫表格看起來好亂
| Years | Groups | Balance |
|---|---|---|
| 2017 | 1-Net Sales | 4035508.167 |
| 2017 | 2-COGS | -2282458 |
| 2017 | 3-Operating Gross Profit | -73739.13333 |
| 2017 | 4-Operating Net Profit | -1475871.451 |
| 2017 | 5-Profit and Loss | 7867576.751 |
| 2018 | 1-Net Sales | 5716035.333 |
| 2018 | 2-COGS | -2784823.8 |
| 2018 | 3-Operating Gross Profit | -533335.6667 |
| 2018 | 4-Operating Net Profit | -1527671.1 |
| 2018 | 5-Profit and Loss | 10561865.9 |
| Groups | Balance-2017 | Balance-2018 |
|---|---|---|
| 1-Net Sales | 4035508.167 | 5716035.333 |
| 2-COGS | -2282458 | -2784823.8 |
| 3-Operating Gross Profit | -73739.13333 | -533335.6667 |
| 4-Operating Net Profit | -1475871.451 | -1527671.1 |
| 5-Profit and Loss | 7867576.751 | 10561865.9 |
最簡單的做法...
2張表各自抓出來在join
Select a.Groups,'Balance-2017'=a.Balance,'Balance-2018'=b.Balance
From (Select Groups,Balance From Table Where Years='2017') As a
Join (Select Groups,Balance From Table Where Years='2018') As b on a.Groups=b.Groups
ACCESS只能吃這樣的SQL，要多敲一些鍵盤。
SELECT
A.Groups
, B.Balance AS [Balance-2017]
, C.Balance AS [Balance-2018]
FROM
(
(SELECT Groups FROM table1
WHERE Years = 2017 OR Years = 2018
GROUP BY Groups) A
LEFT JOIN
(SELECT Groups, Balance FROM table1 WHERE Years = 2017) B
ON A.Groups = B.Groups
)
LEFT JOIN
(SELECT Groups, Balance FROM table1 WHERE Years = 2018) C
ON A.Groups = C.Groups;
select Groups,[2017] as 'Balance-2017',[2018] as 'Balance-2018'
from(
select
Groups,Years,Balance
from Lab1
group by Groups,Years,Balance
) as Temp
PIVOT
(
sum(Balance)
for Years in ([2017],[2018])
)as PivotTable
在DW環境底下, 我會這麼寫.
Select Groups
,SUM(case when Years='2017' then Balance else 0 end) AS Balance-2017
,SUM(case when Years='2018' then Balance else 0 end) AS Balance-2018
From Table
group by Groups
iThome鐵人賽
看影片追技術