iT邦幫忙

0

T-SQL 查詢結果格式轉換

大家好
小弟有一個表格如下:

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

原以為很簡單的用子查詢就可做到,沒想到試了很久都一直出現錯誤訊息,不得其法,煩請知道如何處理的網大協助。

謝謝!!

4
Homura
iT邦高手 1 級 ‧ 2018-06-28 10:37:32
最佳解答

你沒畫表格看起來好亂

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
slime iT邦大師 1 級 ‧ 2018-06-28 10:44:05 檢舉

(小修改, 假如項目在每年可能不同.)

select distinct 表格.Groups , y2017.Balance 'Balance-2017' , y2018.Balance 'Balance-2018'  from 表格
left join 表格 as y2017 on y2017.Years='2017' and 表格.Groups=y2017.Groups
left join 表格 as y2018 on y2018.Years='2018' and 表格.Groups=y2018.Groups
Homura iT邦高手 1 級 ‧ 2018-06-28 11:01:33 檢舉
rogermou iT邦新手 5 級 ‧ 2018-06-28 11:02:45 檢舉

謝謝不好意思,下次改進

3
paicheng0111
iT邦高手 1 級 ‧ 2018-06-28 11:34:53

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;
5
allenlwh
iT邦研究生 2 級 ‧ 2018-06-28 11:57:51
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
Homura iT邦高手 1 級 ‧ 2018-06-28 13:41:47 檢舉

PIVOT看起來很好用

4
fuzzylee1688
iT邦新手 1 級 ‧ 2018-06-28 13:55:26

在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

DW = Dreamweaver?

DataWare House 資料倉儲.

我要發表回答

立即登入回答