典型行列转换问题例子
1.包含两个表
--建立测试环境
create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)
go
--删除测试环境
Drop Table tb1,tb2
2.
--建立测试环境
create table tb2 (id int,type nvarchar(10))
insert into tb2 select 1,'a'
insert into tb2 select 1,'b'
insert into tb2 select 1,'c'
insert into tb2 select 1,'d'
insert into tb2 select 2,'a'
insert into tb2 select 3,'b'
insert into tb2 select 4,'c'
insert into tb2 select 4,'d'
go
--查询处理
create function f_catString(@id int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+type from tb2 where id=@id
return(stuff(@s,1,1,''))
end
go
--调用函数
select id,dbo.f_catString(id) as type from tb2 group by id
go
--删除测试环境
Drop function f_catString
Drop Table tb2
3.又一种情况
--示例数据
create table tb(id int,name varchar(8000))
insert tb select 1,',1,3,4,'
union all select 2,',12,34,67,89,'
go
--转换处理
select top 8000 id=identity(int) into # from syscolumns a,syscolumns b
select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from tb a,# b
where len(a.name)>b.id
and substring(','+a.name,b.id,1)=','
and substring(a.name,b.id,1)<>','
order by a.id,b.id
drop table #
go
--删除测试
drop table tb
/*--结果
id name
----------- -------------
1 1
1 3
1 4
2 12
2 34
2 67
2 89
（所影响的行数为 7 行）
--*/
declare @a table (id int,n nvarchar(100))
insert into @a select 1,',1,3,4,'
insert into @a select 2,',12,34,67,89,'
declare @s nvarchar(4000)
set @s=''
select @s=@s+replace(left(n,len(n)-1),',',' union all select '+quotename(id,'''')+',') from @a
set @s=stuff(@s,1,11,'')
print @s
exec(@s)
4.--建立测试环境
Create table TEST
(id Int,
出库1 Int,
出库2 Int,
入库1 Int)
--插入数据
Insert TEST Values(1, 20, 50, 100)
Insert TEST Values(2, 30, 30, 60)
Insert TEST Values(4, 50, 10, 100)
GO
--测试
Declare @sql Nvarchar(4000)
Set @sql=N'Select Distinct '
Select @sql=@sql+Rtrim(id)+' As ID'+Rtrim(id)+N',(Select SUM(-IsNull(出库1,0)-IsNull(出库2,0)+IsNull(入库1,0)) from TEST Where ID='+Rtrim(id)+N') As 库存'+Rtrim(id)+','
from TEST
Select @sql=Left(@sql,Len(@sql)-1)+ ' from TEST'
EXEC(@sql)
GO
--删除测试环境
Drop table TEST
GO
--结果
/*
ID1 库存1 ID2 库存2 ID3 库存3
1 30 2 0 4 40
*/
--示例数据
create table tb(号码 int)
insert tb select 1
union all select 2
union all select 3
union all select 4
union all select 6
union all select 8
union all select 10
union all select 11
union all select 12
go
--处理
select id=identity(int),号码 into #a from tb a
where not exists(
select * from tb where 号码=a.号码-1)
select id=identity(int),号码 into #b from tb a
where not exists(
select * from tb where 号码=a.号码+1)
select 号段=cast(a.号码 as varchar)
+case a.号码 when b.号码 then '' else '~'+cast(b.号码 as varchar) end,
数量=b.号码-a.号码+1
from #a a,#b b
where a.id=b.id
drop table #a,#b
go
--删除测试
drop table tb
/*--结果
号段 数量
----------- ------
1~4 4
6 1
8 1
10~12 3
（所影响的行数为 4 行）
--*/
create table studentmark(ID int,StudentID int,Kind varchar(16),Mark float,TestTime DateTime,TestKindID int)
insert into studentmark
select 1, 20, '语文',90 ,'2004-05-06' , 1 union all
select 2, 20, '数学',80 ,'2004-05-06' , 1 union all
select 3, 20, '英语',70 ,'2004-05-06' , 1 union all
select 4, 21, '语文',60 ,'2004-05-06' , 1 union all
select 5, 21, '数学',70 ,'2004-05-06' , 1 union all
select 6, 21, '英语',90 ,'2004-05-06' , 1 union all
select 7, 23, '语文',50 ,'2004-05-06' , 1 union all
select 8, 23, '数学',40 ,'2004-05-06' , 1 union all
select 9, 23, '英语',20 ,'2004-05-06' , 1 union all
select 10, 20, '语文',90 ,'2004-02-26' , 2 union all
select 11, 20, '数学',80 ,'2004-02-26' , 2 union all
select 12, 20, '英语',70 ,'2004-02-26' , 2 union all
select 13, 21, '语文',60 ,'2004-02-26' , 2 union all
select 14, 21, '数学',70 ,'2004-02-26' , 2 union all
select 15, 21, '英语',90 ,'2004-02-26' , 2 union all
select 16, 23, '语文',50 ,'2004-02-26' , 2 union all
select 17, 23, '数学',40 ,'2004-02-26' , 2 union all
select 18, 23, '英语',20 ,'2004-02-26' , 2
DECLARE @SQL VARCHAR(8000)
SET @SQL='select distinct StudentID '
SELECT @SQL= @SQL+','''+kind+''' as '+kind+',(select mark from studentmark where kind='''+kind+''' and TestKindID=a.TestKindID and StudentID=a.StudentID) as mark'+kind
from
(select distinct kind from studentmark) b
set @sql=@sql+',TestKindID from studentmark a'
print @sql
exec(@sql)
drop table studentmark
create table tb(id int,事情 nvarchar(20),结果 nvarchar(20))
insert into tb select 1,'吃饭','吃完'
insert into tb select 2,'吃饭','吃完'
insert into tb select 1,'上班','准时到'
go
alter table tb add sid int identity(1,1)
go
select id,事情,结果,sid=(select count(*) from tb where id=a.id and sid<=a.sid) into #t from tb a
--select * from #t
DECLARE @SQL VARCHAR(8000)
SET @SQL='select distinct id '
SELECT @SQL= @SQL+',(select 事情 from #t where sid='''+cast(sid as nvarchar(10))
+''' and id=a.id) as 事情'
+cast(sid as nvarchar(10))
+',(select 结果 from #t where sid='''+cast(sid as nvarchar(10))
+''' and id=a.id) as 结果'
+cast(sid as nvarchar(10))
from
(select distinct sid from #t) b
set @sql=@sql+' from #t a'
print @sql
exec(@sql)
drop table tb,#t
