关于行列转行的问题

[huxin1]

请问如何实现数据的列转行.比如有这么一个表tt(em_id,fl_id,amount)其中em_id是员工号,fl_id分四种,分别是养老金帐号,公积金帐号,医保帐号,失业金账号.amount分别是每种每月所应交的金额.我想用SQL语句实现这样的效果
em_id 养老金帐号 amount 公积金帐号 amount 医保帐号 amount 失业金账号 amount

[zjcxc]

select em_id
,养老金帐号=sum(case fl_id when '养老金帐号' then amount else 0 end)
,公积金帐号=sum(case fl_id when '公积金帐号' then amount else 0 end)
,医保帐号=sum(case fl_id when '医保帐号' then amount else 0 end)
,失业金账号=sum(case fl_id when '失业金账号' then amount else 0 end)
from tt
group by em_id

[chinaandys]

select em_id,养老金账号=sum(case fl_id when '养老金账号' then amount),
公积金帐号=sum(case fl_id when '公积金帐号' then amount),
医保帐号=sum(case fl_id when '医保帐号' then amount),
失业金账号=sum(case fl_id when '失业金账号' then amount)
from tt group by em_id

[chinaandys]

select em_id,养老金账号=sum(case fl_id when '养老金账号' then amount else 0 end),
公积金帐号=sum(case fl_id when '公积金帐号' then amount else 0 end),
医保帐号=sum(case fl_id when '医保帐号' then amount else 0 end),
失业金账号=sum(case fl_id when '失业金账号' then amount else 0 end)
from tt group by em_id

[正宗老冉]

/*
-- 表[tt] 结构及示例数据:
----------------------------------------------
em_id fl_name fl_card_id amount
----------------------------------------------
0005 公积金账号 GJJ00051111 305.00
0005 失业金账号 SYJ00052222 21.00
0005 养老金账号 YLJ00053333 208.00
0005 医保账号 YB000544444 527.00
0006 公积金账号 GJJ00061111 314.00
0006 失业金账号 SYJ00062222 22.00
0006 养老金账号 YLJ00063333 249.00
0006 医保账号 YB000644444 556.00
0007 公积金账号 GJJ00071111 298.00
0007 失业金账号 SYJ00072222 19.00
0007 养老金账号 YLJ00073333 194.00
0007 医保账号 YB000744444 480.00
0008 公积金账号 GJJ00081111 333.00
0008 失业金账号 SYJ00082222 24.00
0008 养老金账号 YLJ00083333 210.00
0008 医保账号 YB000844444 519.00
-----------------------------------------------

-- 转换后的数据:
-----------------------------------------------------------------------------------------------------
人员 养老金账号 金额 公积金账号 金额 医保账号 金额 失业金账号 金额
-----------------------------------------------------------------------------------------------------
0005 YLJ00053333 208.00 GJJ00051111 305.00 YB000544444 527.00 SYJ00052222 21.00
0006 YLJ00063333 249.00 GJJ00061111 314.00 YB000644444 556.00 SYJ00062222 22.00
0007 YLJ00073333 194.00 GJJ00071111 298.00 YB000744444 480.00 SYJ00072222 19.00
0008 YLJ00083333 210.00 GJJ00081111 333.00 YB000844444 519.00 SYJ00082222 24.00
-----------------------------------------------------------------------------------------------------
*/

--行数据
SELECT [em_id] AS [人员编号],[fl_name] AS [账号类别],
[fl_card_id] AS [账号],[amount] AS [金额] FROM [tt]
--行数据转列
SELECT * INTO #T1 FROM [tt] WHERE [fl_name]='养老金账号'
SELECT * INTO #T2 FROM [tt] WHERE [fl_name]='公积金账号'
SELECT * INTO #T3 FROM [tt] WHERE [fl_name]='医保账号'
SELECT * INTO #T4 FROM [tt] WHERE [fl_name]='失业金账号'
SELECT DISTINCT
T0.[em_id] AS [人员编号],
T1.[fl_card_id] AS [养老金账号], T1.[amount] AS [养老金金额],
T2.[fl_card_id] AS [公积金账号], T2.[amount] AS [公积金金额],
T3.[fl_card_id] AS [医保账号], T3.[amount] AS [医保金额],
T4.[fl_card_id] AS [失业金账号], T4.[amount] AS [失业金金额]
FROM [tt] T0
INNER JOIN [#T1] T1 ON T0.[em_id]=T1.[em_id]
INNER JOIN [#T2] T2 ON T0.[em_id]=T2.[em_id]
INNER JOIN [#T3] T3 ON T0.[em_id]=T3.[em_id]
INNER JOIN [#T4] T4 ON T0.[em_id]=T4.[em_id]
DROP TABLE #T1,#T2,#T3,#T4
/************************************************************
* by Ran Ran 2004/8/15 13:52 *
* [e-mail] *
* Beijing Youngsoft Ltd. *
************************************************************/
------------------------------------
<H1><B>地球是圆的吗？</B></H1>
------------------------------------
《CSDN论坛新助手 CSDN's forum Explorer》 1、更快速的浏览 2、更方便地保存 3、更快捷的收/发短信 下载地址：http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe

[正宗老冉]

/*
-- 表[tt] 结构及示例数据:
----------------------------------------------
em_id fl_name fl_card_id amount
----------------------------------------------
0005 公积金账号 GJJ00051111 305.00
0005 失业金账号 SYJ00052222 21.00
0005 养老金账号 YLJ00053333 208.00
0005 医保账号 YB000544444 527.00
0006 公积金账号 GJJ00061111 314.00
0006 失业金账号 SYJ00062222 22.00
0006 养老金账号 YLJ00063333 249.00
0006 医保账号 YB000644444 556.00
0007 公积金账号 GJJ00071111 298.00
0007 失业金账号 SYJ00072222 19.00
0007 养老金账号 YLJ00073333 194.00
0007 医保账号 YB000744444 480.00
0008 公积金账号 GJJ00081111 333.00
0008 失业金账号 SYJ00082222 24.00
0008 养老金账号 YLJ00083333 210.00
0008 医保账号 YB000844444 519.00
-----------------------------------------------

-- 转换后的数据（改造了一下，加显合计列、总计行）
---------------------------------------------------------------------------------------------------------------------
人员 养老金账号 金额 公积金账号 金额 医保账号 金额 失业金账号 金额 合计
---------------------------------------------------------------------------------------------------------------------
0005 YLJ00053333 208.00 GJJ00051111 305.00 YB000544444 527.00 SYJ00052222 21.00 1061.00
0006 YLJ00063333 249.00 GJJ00061111 314.00 YB000644444 556.00 SYJ00062222 22.00 1141.00
0007 YLJ00073333 194.00 GJJ00071111 298.00 YB000744444 480.00 SYJ00072222 19.00 991.00
0008 YLJ00083333 210.00 GJJ00081111 333.00 YB000844444 519.00 SYJ00082222 24.00 1086.00
总计 养老金: 861.00 公积金: 1250.00 医保: 2082.00 失业金: 86.00 4279.00
---------------------------------------------------------------------------------------------------------------------
*/

--行数据
SELECT [em_id] AS [人员编号],[fl_name] AS [账号类别],
[fl_card_id] AS [账号],[amount] AS [金额] FROM [tt]
--行数据转列
SELECT * INTO #T1 FROM [tt] WHERE [fl_name]='养老金账号'
SELECT * INTO #T2 FROM [tt] WHERE [fl_name]='公积金账号'
SELECT * INTO #T3 FROM [tt] WHERE [fl_name]='医保账号'
SELECT * INTO #T4 FROM [tt] WHERE [fl_name]='失业金账号'
CREATE TABLE #T5
([人员编号] char(4) ,
[养老金账号] nvarchar(20),[养老金金额] numeric(18,2),
[公积金账号] nvarchar(20),[公积金金额] numeric(18,2),
[医保账号] nvarchar(20),[医保金额] numeric(18,2),
[失业金账号] nvarchar(20),[失业金金额] numeric(18,2),
[合计金额] numeric(18,2)
) ON [PRIMARY]
INSERT INTO #T5
SELECT DISTINCT
T0.[em_id] AS [人员编号],
T1.[fl_card_id] AS [养老金账号], T1.[amount] AS [养老金金额],
T2.[fl_card_id] AS [公积金账号], T2.[amount] AS [公积金金额],
T3.[fl_card_id] AS [医保账号], T3.[amount] AS [医保金额],
T4.[fl_card_id] AS [失业金账号], T4.[amount] AS [失业金金额],
T1.[amount]+T2.[amount]+T3.[amount]+T4.[amount] AS [合计金额]
FROM [tt] T0
INNER JOIN [#T1] T1 ON T0.[em_id]=T1.[em_id]
INNER JOIN [#T2] T2 ON T0.[em_id]=T2.[em_id]
INNER JOIN [#T3] T3 ON T0.[em_id]=T3.[em_id]
INNER JOIN [#T4] T4 ON T0.[em_id]=T4.[em_id]
SELECT * FROM #T5 UNION
SELECT '总计','养老金:',SUM([养老金金额]),'公积金:',SUM([公积金金额]),
'医保:',SUM([医保金额]),'失业金:',SUM([失业金金额]),SUM([合计金额]) FROM #T5
DROP TABLE #T1,#T2,#T3,#T4,#T5
/************************************************************
* by Ran Ran 2004/8/15 14:19 *
* [e-mail] *
* Beijing Youngsoft Ltd. *
************************************************************/


------------------------------------
天圆地方，云谈风轻
------------------------------------
《CSDN论坛新助手 CSDN's forum Explorer》 1、更快速的浏览 2、更方便地保存 3、更快捷的收/发短信 下载地址：http://www.seeyou.com.cn/CoolSlob/CSDNExplorer.exe

[huxin1]

那如果是这么一个表tt(em_id,fl_name,fl_card_id,amount)其中em_id是员工号,fl_id分四种,分别是养老金帐号,公积金帐号,医保帐号,失业金账号.amount分别是每种每月所应交的金额.
表中数据为
0005 ,'养老金帐号 ','111111',200.00
0005 ,'公积金帐号' ,'222222',300.00
0005 ,'医保帐号 ' ,'3333333',500.00
0005 ,'失业金账号' ,'4444444',20.00
0006,...........................
...................
我想用SQL语句实现这样的效果
em_id 养老金帐号 amount 公积金帐号 amount 医保帐号 amount 失业金账号 amount
0005 111111 200.00 222222 300.00 3333333 500.00 4444444 20.00
0006 .............................................................
.....................................................................