行列互换

╰半橙微兮° 2022-09-26 14:00 210阅读 0赞

\---psydbnew (PSYP\_Trends\_GetTestStressorAvgPoint)  
SELECT QuestionNum,  
     sum(case when BatchClassName='第一批' then AvgPoint else 0 end )as '第一批',  
     sum(case when BatchClassName='第二批' then AvgPoint else 0 end )as '第二批',  
     sum(case when BatchClassName='第三批' then AvgPoint else 0 end )as '第三批',  
     sum(case when BatchClassName='第四批' then AvgPoint else 0 end )as '第四批'  
    FROM (  
        select distinct a.TestID,b.QuestionNum,b.QuestionContent,c.TotalPoint,     
       ([1.\*c.TotalPoint/@TotalCount)as][1._c.TotalPoint_TotalCount_as] AvgPoint, @TotalCount as TotalCount,     
       cbc.BatchClassID,c.BatchClassID AS tmpBatchClassID,cbc.BatchClassName,  
       cbc.BeginDate,cbc.EndDate   
      from V\_R\_Reports as a      
        left outer join PSY\_T\_QuestionBase as b on a.TestID=b.TestID      
        left outer join \#tb as c on b.TestID=c.TestID AND b.QuestionNum=c.QuestionNum     
        left outer join PSY\_U\_CardBatchClass as cbc on a.UnitID=cbc.UnitID AND a.BatchClassID=cbc.BatchClassID     
        where a.UnitID=ltrim(rtrim(@UnitID)) and a.TestID=ltrim(rtrim(@TestID))     
           and a.BatchClassID in (Select Distinct BatchClassID FROM \#tb\_transition)   
           AND a.StaID in (Select Distinct StaID FROM \#tb\_transition)   
           AND a.DepartmentID in (Select Distinct DepartmentID FROM \#tb\_transition)  
           AND a.AreaID in (Select Distinct AreaID FROM \#tb\_transition)  
           AND a.Sex in (Select Distinct Sex FROM \#tb\_transition)      
           and a.Aid in (Select Distinct AgeID FROM \#tb\_transition))as tbs  
    GROUP BY QuestionNum  
\---psydbnew (PSYP\_Trends\_GetTestStressorAvgPoint)  
\--------分列拆分结果  
有表tb, 如下:  
id          value  
\----------- -----------  
1           aa,bb  
2           aaa,bbb,ccc  
欲按id,分拆value列, 分拆后结果如下:  
id          value  
\----------- --------  
1           aa  
1           bb  
2           aaa  
2           bbb  
2           ccc

\--1. 旧的解决方法(sql server 2000)

create table tb(id int,value varchar(30))  
insert into tb values(1,'aa,bb')  
insert into tb values(2,'aaa,bbb,ccc')  
go

\--方法1.使用临时表完成  
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO \# FROM syscolumns a, syscolumns b

SELECT A.id, value = SUBSTRING(A.\[value\], B.id, CHARINDEX(',', A.\[value\] + ',', B.id) - B.id)  
FROM tb A, \# B  
WHERE SUBSTRING(',' + A.\[value\], B.id, 1) = ','

DROP TABLE \#

\--方法2.如果数据量小，可不使用临时表  
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number)  
from tb a join master..spt\_values  b  
on b.type='p' and b.number between 1 and len(a.value)  
where substring(',' + a.value , b.number , 1) = ','

\--2. 新的解决方法(sql server 2005)  
create table tb(id int,value varchar(30))  
insert into tb values(1,'aa,bb')  
insert into tb values(2,'aaa,bbb,ccc')  
go

\--方法1.使用xml完成  
SELECT A.id, B.value FROM  
(  
  SELECT id, \[value\] = CONVERT(xml,'<root><v>' + REPLACE(\[value\], ',', '</v><v>') + '</v></root>') FROM tb  
) A OUTER APPLY  
(  
  SELECT value = N.v.value('.', 'varchar(100)') FROM A.\[value\].nodes('/root/v') N(v)  
) B

\--方法2.使用CTE完成  
;with tt as  
(select id,\[value\]=cast(left(\[value\],charindex(',',\[value\]+',')-1) as nvarchar(100)),Split=cast(stuff(\[value\]+',',1,charindex(',',\[value\]+','),'') as nvarchar(100)) from tb  
union all  
select id,\[value\]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''  
)  
select id,\[value\] from tt order by id option (MAXRECURSION 0)

DROP TABLE tb

\----------列转行（列是动态的）  
create table tb (cp varchar(3),xm01 int,xm02 int,xm03 int,xm04 int)  
insert into tb  
select 'cp1',100,200,300,400 union all  
select 'cp2',10,20,30,40 union all  
select 'cp3',11,22,32,42 union all  
select 'cp4',112,222,321,422  
\---方法1  
select \* from tb  
select cp,xm,sl from tb  
unpivot  
(sl for xm in(xm01,xm02,xm03,xm04)  
)t  
\----方法2  
declare @sql varchar(8000)  
select @sql = isnull(@sql + ' union all ' , '' ) + ' select cp , \[xm\] = ' + quotename(Name , '''') + ' , \[s1\] = ' + quotename(Name) + ' from tb'  
from syscolumns  
where name! = N'cp' and ID = object\_id('tb') --表名tb，不包含列名为cp的其它列  
order by colid asc  
exec(@sql + ' order by cp,xm ')

\---列转行  
if not object\_id('Class') is null  
    drop table Class  
Go  
Create table Class(\[Student\] nvarchar(2),\[数学\] int,\[物理\] int,\[英语\] int,\[语文\] int)  
Insert Class  
select N'李四',77,85,65,65 union all  
select N'张三',87,90,82,78

\----实现  
declare @s nvarchar(4000)  
select @s=isnull(@s+',','')+quotename(Name)  
from syscolumns where ID=object\_id('Class') and Name not in('Student')  
order by Colid  
exec('select Student,\[Course\],\[Score\] from Class unpivot (\[Score\] for \[Course\] in(['+@s+'))b'][s_b])

go  
select  
    Student,\[Course\],\[Score\]  
from  
    Class  
unpivot  
    (\[Score\] for \[Course\] in(\[数学\],\[物理\],\[英语\],\[语文\]))b

\----动态行转列  
create table tb(Title nvarchar(20))  
insert into tb values(N'标题1')  
insert into tb values(N'标题2')  
insert into tb values(N'标题3')  
insert into tb values(N'标题4')  
insert into tb values(N'标题5')  
go

declare @sql nvarchar(4000)  
select @sql = isnull(@sql + '\],\[' , '') + Title from (select top 30 \* from tb order by title) t group by Title  
set @sql = '\[' + @sql + '\]'  
exec ('select \* from (select top 30 \* from tb order by title) a pivot (max(Title) for Title in (' + @sql + ')) b')

drop table tb

/\*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换\*/  
create proc p\_zj  
       @tbname sysname, --要处理的表名  
       @fdname sysname, --做为转换的列名  
       @new\_fdname sysname='' --为转换后的列指定列名  
as  
declare @s1 varchar(8000) , @s2 varchar(8000),  
        @s3 varchar(8000) , @s4 varchar(8000),  
        @s5 varchar(8000) , @i varchar(10)  
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'  
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',  
       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new\_fdname , '') when '' then ''  
       else @new\_fdname + '=' end + '''''' + name + '''''''',  
       @s3 = @s3 + 'select @' + @i + ['=@'][s_b] \+ @i + '+'',\['' + \[' + @fdname +  
       '\]+''\]=''+cast(\[' + name + '\] as varchar) from \[' + @tbname + '\]',  
       @s4 = @s4 + ',@' + @i + '=''select [''+@'][s_b] \+ @i,  
       @s5 = @s5 + '+'' union all [''+@'][s_b] \+ @i,  
       @i=cast(@i as int)+1  
from syscolumns  
where object\_id(@tbname)=id and name<>@fdname

select @s1=substring(@s1,2,8000),  
       @s2=substring(@s2,2,8000),  
       @s4=substring(@s4,2,8000),  
       @s5=substring(@s5,16,8000)  
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '  
exec(' + @s5 + ')')  
go

\--创建测试数据  
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)  
insert Test  
select '1月',100,200,300 union all  
select '2月',110,210,310 union all  
select '3月',120,220,320 union all  
select '4月',130,230,330  
go

\--用上面的存储过程测试：  
exec p\_zj 'Test', '月份' , '项目'

drop table Test  
drop proc p\_zj

/\*  
项目      1月      2月      3月      4月  
\--------  ------   -------- -------- --------  
奖金      300      310      320      330  
工资      100      110      120      130  
福利      200      210      220      230

（所影响的行数为 3 行）  
\*/

[1._c.TotalPoint_TotalCount_as]: mailto:1.*c.TotalPoint/@TotalCount%29as
[s_b]: mailto:'%20rel=