当前位置:  数据库>sqlserver

sqlserver2005 行列转换实现方法

    来源: 互联网  发布时间:2014-09-05

    本文导语:  代码如下:--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(5...

代码如下:

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

    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 连接sqlserver的url是jdbc:microsoft:sqlserver://192.168.0.100:1433,他连那一个数据库呢?
  • (100分)用Microsoft jdbc Driver for sqlserver2000(type4)访问sqlserver2k的问题,help!
  • 通过SQLSERVER重启SQLSERVER服务和计算机的方法
  • 怎样用文JDBC for sqlserver访问SQLSERVER 数据库请大侠给出一个完整的程序代码
  • (100分)jdbc连接sqlserver 200出错.[SQLServer 2000 Driver for JDBC]Error establishing socket.
  • 如何连接sqlserver2000数据库
  • 深入JDBC sqlserver连接写法的详解
  • how to 连接到sqlserver2000?
  • 请问如何直接sqlserver?
  • SQLServer JDBC Driver
  • 在sqlserver2000时中文问题。
  • 设置密码保护的SqlServer数据库备份文件与恢复文件的方法
  • 如何使用JSP 连接SQLSERVER数据库,请不吝赐教!(在线等,急救!)
  • 简单实现SQLServer转MYSQL的方法
  • 求助!SQLServer2000 JDBC Driver 不能加载?
  • Resin+Jsp+Sqlserver2000的问题!在线等候!
  • applet用jdbc链接SQLServer 2000的问题
  • 请问连接SQLServer 的驱动器是什么呢?
  • jdbc:microsoft:sqlserver://localhost:1433;DatabaseNameOPTIONAL=webmail???
  • 如何用SQLServer2k-JDBC连接数据库?


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3