欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sqlserver2005 行列转换实现方法

程序员文章站 2023-12-12 17:22:58
复制代码 代码如下:--create company table create table company ( comid varchar(50) primary key,...
复制代码 代码如下:

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

上一篇:

下一篇: