sqlserver2005 行列转换实现方法

复制代码代码如下:
--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）