• 企业400电话
  • 网络优化推广
  • AI电话机器人
  • 呼叫中心
  • 全 部 栏 目

    网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    sqlserver2005 行列转换实现方法
    POST TIME:2021-10-18 19:01
    复制代码 代码如下:

    --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)
    您可能感兴趣的文章:
    • 行转列之SQL SERVER PIVOT与用法详解
    • sql server通过pivot对数据进行行列转换的方法
    上一篇:sql convert函数使用小结
    下一篇:跨服务器查询导入数据的sql语句
  • 相关文章
  • 

    关于我们 | 付款方式 | 荣誉资质 | 业务提交 | 代理合作


    © 2016-2020 巨人网络通讯

    时间:9:00-21:00 (节假日不休)

    地址:江苏信息产业基地11号楼四层

    《增值电信业务经营许可证》 苏B2-20120278

    X

    截屏,微信识别二维码

    微信号:veteran88

    (点击微信号复制,添加好友)

     打开微信