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)