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 tables row
--to new tables 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)