典型行列轉換問題例子

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