SQL Server 2005 行列转换实现方法

这篇文章主要为大家详细介绍了SQL Server 2005 行列转换实现方法，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

--Create Company Table 
Create Table Company 
( 
ComID varchar(50) primary key, 
ComName nvarchar(50) not null, 
ComNumber varchar(50) not null, 
ComAddress nvarchar(200), 
ComTele varchar(50) 
) 
--Create Product Table 
Create Table Product 
( 
ProductID varchar(50) primary key, 
ComID varchar(50) not null, 
ProName nvarchar(200) not null, 
ProNumber int not null 
) 
select * from product 
--insert into table value 
insert Company select('58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','CompanyOne','SH19991028','ShangHai','98765432112') 
union all select('768B2E84-0AAB-4653-8F5B-5EF6165204DB','CompanyTwo','SH19991028','ShangHai','98765432113') 
union all select('AAE86C36-C82B-421D-BC55-E72368B1DE00','CompanyThree','SH19991028','ShangHai','98765432114') 
union all select('C672B359-C800-47DE-9BB4-6D0FC614594C','CompanyFour','SH19991028','ShangHai','98765432115') 
union all select('FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','CompanyFive','SH19991028','ShangHai','98765432116') 
insert Product sleect('1598A60B-FCFD-4269-864B-CB999E8EA5CA','AAE86C36-C82B-421D-BC55-E72368B1DE00','SqlServer2005',500) 
union all select('19D7BF2F-79FD-414E-B648-F105D4AB1EBB','AAE86C36-C82B-421D-BC55-E72368B1DE00','Office',400) 
union all select('232B6109-C575-4316-A9BD-0C58F737BE7B','FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','SqlServer2005',200) 
union all select('4F30E12C-7654-40CC-8245-DF1C3453FBC5','768B2E84-0AAB-4653-8F5B-5EF6165204DB','Office',400) 
union all select('54C6E4C2-1588-43DF-B22C-0697A1E27DB0','58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','Office',400) 
union all select('551EB6CA-3619-4250-98A0-7231BB4C3D58','FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','SqlServer2000',100) 
union all select('5BAD331C-B6E4-440E-AC54-52CE13166843','768B2E84-0AAB-4653-8F5B-5EF6165204DB','SqlServer2005',1000) 
union all select('5C039C53-2EE4-4D90-BA78-7A20CEC4935C','58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','Windows2000',200) 
union all select('673A8683-CD03-40D2-9DB1-1ADA812016E2','58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','WindowsXP',100) 
union all select('6B9F771B-46EA-4496-B1DA-F10CB53F6F62','C672B359-C800-47DE-9BB4-6D0FC614594C','WindowsXP',100) 
union all select('770089B1-A80A-4F48-8537-E15BD00A99E7','AAE86C36-C82B-421D-BC55-E72368B1DE00','WindowsXP',100) 
union all select('92EED635-5C61-468A-B19D-01AAC112D8A3','FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','SysBase',100) 
union all select('99195297-F7F0-4DCD-964E-CFB8A162B6D0','768B2E84-0AAB-4653-8F5B-5EF6165204DB','Windows2008',300) 
union all select('9EF017C1-F8F0-49BC-A7BD-4DFFB6EA8037','768B2E84-0AAB-4653-8F5B-5EF6165204DB','Windows2000',200) 
union all select('A31BCD44-7856-461F-A0FD-407DCA96E8A9','C672B359-C800-47DE-9BB4-6D0FC614594C','SqlServer2005',100) 
union all select('A9B52E8F-129F-4113-A473-D4BDD2B3C09C','768B2E84-0AAB-4653-8F5B-5EF6165204DB','WindowsXP',100) 
union all select('AC228CA0-490C-4B3D-866D-154E771B2083','58C0F3FD-7B98-4E74-A1A8-7B144FCB8707','Windows2008',300) 
union all select('BD0BA1D3-D1D2-4BC7-9EFD-78B1165060A0','FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','DB2',200) 
union all select('CAA71AEA-7130-4AB8-955E-B04EA35A178A','FDBA8B3F-1851-4B73-9A20-A24AEF721AAE','Oracle',100) 
--This is Business pack . 
--Using this function can using table's row 
--to new table's column 
declare @strSql varchar(1000) 
declare @column varchar(50) 
declare @columns varchar(200) 
set @columns = '' 
/*According to Cursor get new table column*/ 
declare varchar_cur cursor for 
select distinct proname from product order by proname 
open varchar_cur 
fetch next from varchar_cur into @column 
while @@fetch_status = 0 
begin 
set @columns = @columns + '[' + @column + '],' 
fetch next from varchar_cur into @column 
end 
Close varchar_cur 
Deallocate varchar_cur 
/*Converted to the ranks of the use of pivot*/ 
set @columns = stuff(@columns,len(@columns),1,'') 
set @strSql = 'select comname,' + @columns 
set @strSql = @strSql + ' from ' 
set @strSql = @strSql + ' (' 
set @strSql = @strSql + ' select comname,pronumber,proname from product' 
set @strSql = @strSql + ' left join company on product.comid = company.comid ' 
set @strSql = @strSql + ' ) as temp' 
set @strSql = @strSql + ' pivot ' 
set @strSql = @strSql + ' ( ' 
set @strSql = @strSql + ' sum(pronumber) ' 
set @strSql = @strSql + ' for proname in (' + @columns + ') ' 
set @strSql = @strSql + ' ) as Pivot_table' 
exec(@strSql)

注：关于SQL Server 2005 行列转换实现方法的内容就先介绍到这里，更多相关文章的可以留意