sqlserver2005 中行列转换

最新推荐文章于 2024-11-16 22:26:46 发布

facingfeng_xf

最新推荐文章于 2024-11-16 22:26:46 发布

阅读量554

点赞数 1

文章标签： sqlserver table pivot windows office c

本文链接：https://blog.csdn.net/facingfeng_xf/article/details/4704438

版权

--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)