数据旋转问题求解!

[yiyishuitian]

想实现下面表的旋转,不知道有什么好的方法,故求解,不胜感激!

如何从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

[东那个升]

2005以上版本可以试试如下

-- tb2->tb1
SELECT name,id,[monday], [tuesday], [wednesday], [thursday], [friday],[saturday],[sunday]
FROM tb2
PIVOT
(
max (number)
FOR theday IN
( [monday], [tuesday], [wednesday], [thursday], [friday],[saturday],[sunday] )
) AS pvt
ORDER BY id;

-- tb1->tb2
SELECT name,id, theday, number
FROM   tb1
UNPIVOT
    ( number FOR theday IN 
        ([monday], [tuesday], [wednesday], [thursday], [friday],[saturday],[sunday])
)AS unpvt

[-狙击手-]

[feixianxxx]

LS正解。。。。。。。。。

[yiyishuitian]

论坛速度太快了,发贴没有十几分钟就有回贴了.谢谢!俺还想学习一下 SQL 2000 中是怎么解决的.

[百年树人]

sql2000

--1.
select name,id,'monday' as theday,monday from tb1
union all
select name,id,'tuesday' as theday,tuesday from tb1
union all
select name,id,'wednesday' as theday,wednesday from tb1
union all
select name,id,'thursday' as theday,thursday from tb1
union all
select name,id,'friday' as theday,friday from tb1
union all
select name,id,'saturday' as theday,saturday from tb1
union all
select name,id,'sunday' as theday,sunday from tb1
order by 1,2

/**
name       id         theday    monday     
---------- ---------- --------- ---------- 
小李         00003      monday    10
小李         00003      tuesday   50
小李         00003      wednesday 40
小李         00003      thursday  20
小李         00003      friday    10
小李         00003      saturday  30
小李         00003      sunday    50
小刘         00005      sunday    100
小刘         00005      friday    20
小刘         00005      saturday  10
小刘         00005      thursday  60
小刘         00005      wednesday 20
小刘         00005      tuesday   50
小刘         00005      monday    20
小王         00002      monday    20
小王         00002      tuesday   30
小王         00002      wednesday 20
小王         00002      thursday  20
小王         00002      friday    20
小王         00002      saturday  30
小王         00002      sunday    50
小张         00001      sunday    30
小张         00001      saturday  10
小张         00001      friday    20
小张         00001      wednesday 20
小张         00001      thursday  20
小张         00001      monday    10
小张         00001      tuesday   10
小赵         00004      tuesday   30
小赵         00004      monday    40
小赵         00004      wednesday 20
小赵         00004      thursday  20
小赵         00004      friday    20
小赵         00004      saturday  30
小赵         00004      sunday    50

（所影响的行数为 35 行）

**/

--2.
select name,id,
max(case when theday='monday' then number else 0 end) as [monday],
max(case when theday='tuesday' then number else 0 end) as [tuesday],
max(case when theday='wednesday' then number else 0 end) as [wednesday],
max(case when theday='thursday' then number else 0 end) as [thursday],
max(case when theday='friday' then number else 0 end) as [friday],
max(case when theday='saturday' then number else 0 end) as [saturday],
max(case when theday='sunday' then number else 0 end) as [sunday]
from tb2
group by name,id

/**
name       id         monday      tuesday     wednesday   thursday    friday      saturday    sunday      
---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
小张         00001      10          10          20          20          20          10          10
小王         00002      20          30          20          20          20          30          50

（所影响的行数为 2 行）
**/

[feixianxxx]

select name,ID,
monday=MAX(case when theday='theday' then number end),
tuesday=MAX(case when theday='tuesday' then number end),
wednesday=MAX(case when theday='wednesday' then number end),
thursday=MAX(case when theday='thursday' then number end),
friday=MAX(case when theday='friday' then number end),
saturday=MAX(case when theday='saturday' then number end),
sunday=MAX(case when theday='sunday' then number end)
from tb2
group by name,ID

[yiyishuitian]

谢谢回复的所有的老师们!
josy 老师 的 union all 的 方法我平时也用,但是有时候会用到把两三年的数据按月旋转过来,union 起来语句太长了.要union all 30多少才行,不知道动态的语句好不好写呢.