有關于把某個字段里的數據，轉換成列名（行轉列）

Question

SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE
,[BUDGET_DETAILS].AMOUT
,CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN '簽呈金額'
WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN '報銷金額'
WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN '預支金額'
WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN '請購金額'
WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN '保留金額'
WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN '沖帳金額'
END SUMCLASS
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
執行以上SQL語句得到以下數據
------------------------------------------------------------------
ID    BUDGETSUM    SURPLUSSUM    BUEGETTYPE    CURRENCYTYPE    AMOUT    SUMCLASS    DEPARTMENTNAME    SUBITEMNAME    COSTNAME
254    100.00      100.00        月度            CNY             0.00     簽呈金額      信息部              修理費            管理費用
255    1000.00     719.00        月度            CNY             100.00   請購金額      信息部              物料消耗          管理費用
255    1000.00     719.00        月度            CNY             60.00    請購金額      信息部              物料消耗          管理費用
255    1000.00     719.00        月度            CNY             70.00    請購金額      信息部              物料消耗          管理費用
255    1000.00     719.00        月度            CNY             36.00    請購金額      信息部              物料消耗          管理費用
---------------------------------------------------------------------
我現在要把SUMCLASS字段里的數據分組變成列，SUBITEMNAME字段分組MAX
（例如：簽呈金額 請購金額）
        0.00    256.00
請問要SQL語句要怎么寫？

Answer 1

SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE    WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
        WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
        WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE    WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
        WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE,sum(CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN [BUDGET_DETAILS]else 0) AS
['簽呈金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN [BUDGET_DETAILS]' else 0 end) as ['報銷金額']
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN [BUDGET_DETAILS] else 0 end) as ['預支金額']
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN [BUDGET_DETAILS] else 0 end) as ['請購金額']
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN [BUDGET_DETAILS] else 0 end) as'保留金額'
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN [BUDGET_DETAILS] else 0 end) as ['沖帳金額']
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
group by SUBITEMNAME;

Answer 2

忘加标点了
SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE    WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
        WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
        WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE    WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
        WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE,sum(CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN [BUDGET_DETAILS]else 0) AS
['簽呈金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN [BUDGET_DETAILS]' else 0 end) as ['報銷金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN [BUDGET_DETAILS] else 0 end) as ['預支金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN [BUDGET_DETAILS] else 0 end) as ['請購金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN [BUDGET_DETAILS] else 0 end) as ['保留金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN [BUDGET_DETAILS] else 0 end) as ['沖帳金額'],
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
group by SUBITEMNAME;

Answer 3

SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE    WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
        WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
        WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE    WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
        WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE,sum(CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN [BUDGET_DETAILS]else 0) AS
['簽呈金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN [BUDGET_DETAILS]' else 0) as ['報銷金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN [BUDGET_DETAILS] else 0) as ['預支金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN [BUDGET_DETAILS] else 0) as ['請購金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN [BUDGET_DETAILS] else 0) as ['保留金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN [BUDGET_DETAILS] else 0) as ['沖帳金額'],
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
group by SUBITEMNAME;

Answer 4

SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE    WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
        WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
        WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE    WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
        WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE,sum(CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN [BUDGET_DETAILS]else 0) AS
['簽呈金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN [BUDGET_DETAILS]' else 0) As ['報銷金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN [BUDGET_DETAILS] else 0) As ['預支金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN [BUDGET_DETAILS] else 0) As ['請購金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN [BUDGET_DETAILS] else 0) As ['保留金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN [BUDGET_DETAILS] else 0) As ['沖帳金額'],
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
group by SUBITEMNAME;

Answer 5

SELECT 
[BUDGET].[ID]
,[BUDGET].[BUDGETSUM]
,[BUDGET].[SURPLUSSUM]
,CASE    WHEN [BUDGET].[BUDGETTYPE]=0 THEN '月度'
        WHEN [BUDGET].[BUDGETTYPE]=1 THEN '季度'
        WHEN [BUDGET].[BUDGETTYPE]=2 THEN '年度'
END BUDGETTYPE
,CASE    WHEN [BUDGET].[CURRENCYTYPE]=0 THEN 'CNY'
        WHEN [BUDGET].[CURRENCYTYPE]=1 THEN 'USD'
END CURRENCYTYPE,sum(CASE WHEN [BUDGET_DETAILS].SUM_CLASS_ID=1 THEN [BUDGET_DETAILS]else 0) AS
['簽呈金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=2 THEN [BUDGET_DETAILS]else 0) As ['報銷金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=3 THEN [BUDGET_DETAILS] else 0) As ['預支金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=4 THEN [BUDGET_DETAILS] else 0) As ['請購金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=5 THEN [BUDGET_DETAILS] else 0) As ['保留金額'],
sum(case WHEN [BUDGET_DETAILS].SUM_CLASS_ID=6 THEN [BUDGET_DETAILS] else 0) As ['沖帳金額'],
,SYS_DEPARTMENT.[NAME] DEPARTMENTNAME
,SUBITEM.[NAME] SUBITEMNAME
,COST.[NAME] COSTNAME
FROM [BUDGET],SYS_DEPARTMENT,SUBITEM,DEPARTMENTCOST,COST
,[BUDGET_DETAILS]
WHERE [BUDGET].ID=[BUDGET_DETAILS].BUDGET_ID
AND [BUDGET].[DEPARTMENT_ID]=SYS_DEPARTMENT.ID
AND [BUDGET].[SUBITEM_ID]=SUBITEM.ID
AND [BUDGET].[DEPARTMENT_ID]=DEPARTMENTCOST.DEPARTMENT_ID
AND DEPARTMENTCOST.COST_ID=COST.ID
group by SUBITEMNAME;

Answer 6

少个END,你添上好了.在case when then end

Answer 7

这个已经有通用代码，我有个T-SQL模板，不过不方面发给你

Answer 8

sql2005上的话，看看pivot/unpivot函数
http://technet.microsoft.com/en-us/library/ms177410.aspx