如何将表中的一列记录转换成栏头（行），另一列记录转换成栏头下对应的记录（行），＃＃数据表列转换成行？(100分)

war_cryer · 2008-05-22

A表结构如下： 　姓名　部门　工资项　值　 　张三　A部　　底薪　１０００ 　张三　A部　　补贴　２００ 　张三　A部　　奖金　１０００ 　李四　B部　　提成　１２００ 　李四　B部　　底薪　１０００ 　李四　B部　　补贴　２００ 　李四　B部　　奖金　１０００ 通过SQL　实现要求如下： 　姓名　部门　底薪　　　补贴　　　　　奖金　　　　提成 　张三　A部　１０００　　２００　　　１０００　　０ 　李四　B部　１０００　　２００　　　１０００　　１２００ 　请问此SQL如何实现，谢谢！

war_cryer · 2008-05-22

DECLARE @Temp nvarchar(4000) DECLARE @Sql nvarchar(4000) set @Temp=N'' DECLARE Employee_Cursor CURSOR FOR --SELECT item_name FROM t_item select item_name from t_Item where item_name in( select  name from syscolumns where id=object_id('t_City')) OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor INTO @AA WHILE @@FETCH_STATUS = 0 BEGIN    IF @Temp = N'' BEGIN         Set @Temp = N'SELECT  '+ rtrim(@AA)         END    ELSE         BEGIN          Set @Temp = @Temp +N','+ rtrim(@AA)         END    FETCH NEXT FROM Employee_Cursor INTO @AA END Set @Temp =  rtrim(@Temp) + N'  FROM t_city' Print @Temp EXEC sp_executesql  @Temp CLOSE Employee_Cursor DEALLOCATE Employee_Cursor ============================= --行列转换行转列 DROP TABLE t_change_lc; CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER); INSERT INTO t_change_lc SELECT 001 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4 UNION SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4; SELECT * FROM t_change_lc; SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1; --行列转换列转行 DROP TABLE t_change_cl; CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1; SELECT * FROM t_change_cl; SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t ORDER BY 1, 2; --行列转换行转列合并 DROP TABLE t_change_lc_comma; CREATE TABLE t_change_lc_comma AS SELECT card_code,quarter_||q AS q FROM t_change_lc; SELECT * FROM t_change_lc_comma; SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q,

), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code; --行列转换列转行分割 DROP TABLE t_change_cl_comma; CREATE TABLE t_change_cl_comma AS SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q,

), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code; SELECT * FROM t_change_cl_comma; SELECT t.card_code, substr(t.q, instr(; || t.q, ;, 1, rn), instr(t.q || ;, ;, 1, rn) - instr(; || t.q, ;, 1, rn)) q FROM (SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b WHERE instr(; || a.q, ;, 1, rn) > 0) t ORDER BY 1, 2;

kaida · 2008-05-22

select distinct(姓名), 部门,   (select 值 from testTable t2 where t2.姓名=t1.姓名 and 工资项='底薪')as "底薪",   (select 值 from testTable t2 where t2.姓名=t1.姓名 and 工资项='补贴')as "补贴",   (select 值 from testTable t2 where t2.姓名=t1.姓名 and 工资项='奖金')as "奖金",   (select 值 from testTable t2 where t2.姓名=t1.姓名 and 工资项='提成')as "提成"  from testTable t1

fas2000 · 2008-05-22

啊,怎么这么多人这东西啊 才上来就看到两个贴子,给你个通用的 if  exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CrossTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop proceudre alter PROCEDURE CrossTable --交叉表生成器 --(Select Distinct SuppCode As SCode,MCode,PurQtyRate As Rate --        From MtSPM A Inner Join MtSPD B On A.ID=B.MtSuppPriceM_ID --                     Inner Join Suppliers C On A.Supp_ID=C.ID --        Where A.Active_KID=120 And AduitPass=1) D @vSourceTAB As Varchar(2000), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名:如上注释段) @vGroupbyField As Varchar(100), --被selct Group By 要显示出来的,可以多个字段(记录可以有空值) @vTransFormCol As Varchar(50), --交叉表中的合计等函数计算值的字段 @vFunction As Varchar(100)=' Sum', --默认值,交叉表中的函数,也可以是' 2*Sum'的计算公式 @vPivotCol As Varchar(100), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值) @vStrWhere As Varchar(1000) =Null, --Where 约束条件,可以为空 @vGroupbyFieldName As Varchar(50)=null --如果GroupField是表达式,则这里一定要指定名称,不然会出错 AS --重要提示

StrSql的Largest size allowed Is 8000,因此尽量将少的字段内容转换为列 Declare @StrSql As Varchar(8000) --//总的SQL语句 Declare @StrSum As Varchar(3000) --//列合计 Declare @pCols As Varchar(100) Declare @StrWhere As Varchar(500) if @vGroupbyFieldName is null set @vGroupbyFieldName=@vGroupbyField set @strsql=' Declare CursorCross Cursor For             Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB +' Order By ' + @vPivotCol + ' For Read only ' Print @StrSql Execute(@StrSql) Begin   Set Nocount On   Set @StrSql =''   Set @StrSum=''   Set @pCols=''   IF Rtrim(Ltrim(IsNull(@vStrWhere,''))) <> ''   Begin     Set @StrWhere=' Where ' + @vStrWhere + ' '   End Else   Set @StrWhere=''   Open CursorCross   While (0=0)   Begin     Fetch Next From CursorCross Into @pCols     IF (@@Fetch_Status<>0) Break     IF @pCols Is  Null --//不为空值,     Set @pCols='Null'     --为了防止新创建的列的标题名称,与@vGroupbyField中的字段重名,     --新创建的列的标题名称都增加前缀[@vGroupbyField.新创建的列的标题名称] --因Sql长度限制Max=8000,由源数据控制字段值不能为Null,因此这里不再检验值是否为Null     Set @StrSql=@StrSql +',' + @vFunction +         '(Case '+@vPivotCol+' When ''' + @pCols+ ''' Then '+@vTransFormCol +' Else Null End) As '+         '['+/*Left(@vPivotCol,1)+*/@pCols+']' --因长度限制,不计算列间之和 --    Set @StrSum=@StrSum + '+IsNull(A.' + '['+Left(@vPivotCol,1)+@pCols+']' +',0)'   End   Set  @StrSql = ' Select ' + @vGroupByField +' AS '+@vGroupbyFieldName+ ' ' +@StrSql + ' From ' +@vSourceTAB+ ' ' + @StrWhere +                  ' Group By ' + @vGroupByField   --列向合计为字段名'TotalSum',合计方法改为重新从源表中取值,减少SQL的字符量   Set @StrSql ='Select A.*,(select '+                @vFunction+'(' +@vTransFormCol +') from '+ @vSourceTAB+@StrWhere+' Group by '+@vGroupbyField+ ' having '+@vGroupbyField+'=A.'+@vGroupbyFieldName                +') As TotalSum From (' + @StrSql  +') As A'   Set @StrSql ='Select A.* into #CrossTable From ('+@StrSql+') As A  Order By '+@vGroupByFieldName   Print @StrSql   Execute(@StrSql)   IF @@Error <>0     Return @@Error   Close CursorCross   Deallocate CursorCross   Return 0 End go

如何将表中的一列记录转换成栏头（行），另一列记录转换成栏头下对应的记录（行），＃＃数据表列转换成行？(100分)

war_cryer

Unregistered / Unconfirmed

war_cryer

Unregistered / Unconfirmed

kaida

Unregistered / Unconfirmed

fas2000

Unregistered / Unconfirmed

Similar threads