首页 > 代码库 > 行列互换
行列互换
---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 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 = http://www.mamicode.com/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 = http://www.mamicode.com/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 = http://www.mamicode.com/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‘)
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 + ‘=@‘ + @i + ‘+‘‘,[‘‘ + [‘ + @fdname +
‘]+‘‘]=‘‘+cast([‘ + name + ‘] as varchar) from [‘ + @tbname + ‘]‘,
@s4 = @s4 + ‘,@‘ + @i + ‘=‘‘select ‘‘+@‘ + @i,
@s5 = @s5 + ‘+‘‘ union all ‘‘+@‘ + @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 行）
行列互换