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)