none
数据旋转问题求解! RRS feed

  • 问题

  • 见贴新年好!俺想实现下面表的旋转,不知道有什么好的方法,故求解,不胜感激! 最好是使用动态SQL语句,因为俺主要是在SQL server 2000中使用.

    如何从tb1 旋转至 tb2
    以及
    如何从tb2 旋转至 tb1

    create table tb1(name varchar(10),id varchar(10),monday varchar(10),tuesday varchar(10),wednesday varchar(10),thursday varchar(10),friday varchar(10),saturday varchar(10),sunday varchar(10))
    insert into tb1
    select '小张','00001','10','10','20','20','20','10','30' union all
    select '小王','00002','20','30','20','20','20','30','50' union all
    select '小李','00003','10','50','40','20','10','30','50' union all
    select '小赵','00004','40','30','20','20','20','30','50' union all
    select '小刘','00005','20','50','20','60','20','10','100'


    create table tb2(name varchar(10),id varchar(10),theday varchar(10),number varchar(10))
    insert into tb2
    select '小张','00001','monday','10' union all
    select '小张','00001','tuesday','10' union all
    select '小张','00001','wednesday','20' union all
    select '小张','00001','thursday','20' union all
    select '小张','00001','friday','20' union all
    select '小张','00001','saturday','10' union all
    select '小张','00001','sunday','10' union all
    select '小王','00002','monday','20' union all
    select '小王','00002','tuesday','30' union all
    select '小王','00002','wednesday','20' union all
    select '小王','00002','thursday','20' union all
    select '小王','00002','friday','20' union all
    select '小王','00002','saturday','30' union all
    select '小王','00002','sunday','50'

    select *from tb1
    select *from tb2
    2010年2月22日 8:58

答案

  • declare @s varchar(8000)
    select @s = isnull(@s+'select name,id,'''+name+''' as theday,'+name+' from tb1
    union all
    ' ,'') from syscolumns where id = object_id('tb1') and name not in('name','id')
    set @s = left(@s,len(@s)-11)
    exec(@s)

    More: blog.csdn.net/happyflystone
    2010年2月22日 13:30
  • 第二个:

    declare @s varchar(8000)
    select @s = isnull(@s+',','')+'max(case when theday = '''+theday+''' then number else 0 end) as '''+theday+''''
    from (select distinct theday from tb2) a
    exec('select id,name,'+ @s+' from tb2 group by id,name')
    
    id         name       friday      monday      saturday    sunday      thursday    tuesday     wednesday   
    ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
    00002      小王         20          20          30          50          20          30          20
    00001      小张         20          10          10          10          20          10          20
    

    More: blog.csdn.net/happyflystone
    2010年2月22日 13:35
  • SQL2005以上版本用pivot/unpivot
    
    --tb1
    declare @s nvarchar(4000)
    select @s=isnull(@s+',','')+quotename(Name)
    from syscolumns where ID=object_id('tb1') and Name not in('name','id') 
    order by Colid
    exec('select name,[id],[theday],[number] from tb1 unpivot ([number] for [theday] in('+@s+'))b')
    
    --tb2
    declare @s nvarchar(4000)
    Select     @s=isnull(@s+',','')+quotename([theday]) from tb2 group by[theday] --isnull(@s+',','') 去掉字符串@s中第一个逗号
    exec('select [name],id,'+@s+' 
    from tb2
    pivot (max([number]) for [theday] in('+@s+'))b ')

    ROY WU(吳熹)
    2010年2月22日 15:39
    版主

全部回复

  • 楼主你好,使用 PIVOT 和 UNPIVOT 可以达到你的要求,具体语法请参考联机丛书
    2010年2月22日 9:20
    版主
  • declare @s varchar(8000)
    select @s = isnull(@s+'select name,id,'''+name+''' as theday,'+name+' from tb1
    union all
    ' ,'') from syscolumns where id = object_id('tb1') and name not in('name','id')
    set @s = left(@s,len(@s)-11)
    exec(@s)

    More: blog.csdn.net/happyflystone
    2010年2月22日 13:30
  • 第二个:

    declare @s varchar(8000)
    select @s = isnull(@s+',','')+'max(case when theday = '''+theday+''' then number else 0 end) as '''+theday+''''
    from (select distinct theday from tb2) a
    exec('select id,name,'+ @s+' from tb2 group by id,name')
    
    id         name       friday      monday      saturday    sunday      thursday    tuesday     wednesday   
    ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
    00002      小王         20          20          30          50          20          30          20
    00001      小张         20          10          10          10          20          10          20
    

    More: blog.csdn.net/happyflystone
    2010年2月22日 13:35
  • SQL2005以上版本用pivot/unpivot
    
    --tb1
    declare @s nvarchar(4000)
    select @s=isnull(@s+',','')+quotename(Name)
    from syscolumns where ID=object_id('tb1') and Name not in('name','id') 
    order by Colid
    exec('select name,[id],[theday],[number] from tb1 unpivot ([number] for [theday] in('+@s+'))b')
    
    --tb2
    declare @s nvarchar(4000)
    Select     @s=isnull(@s+',','')+quotename([theday]) from tb2 group by[theday] --isnull(@s+',','') 去掉字符串@s中第一个逗号
    exec('select [name],id,'+@s+' 
    from tb2
    pivot (max([number]) for [theday] in('+@s+'))b ')

    ROY WU(吳熹)
    2010年2月22日 15:39
    版主
  • 感谢回贴,谢谢.

    2010年2月23日 0:44
  • 感谢回贴,谢谢.

    2010年2月23日 0:44