求SQL高手處理行和列的問題

[candy20131]

有A表
NO T1 T2 T3 T4
1301 8 2
1302 3 5 6 10
1303 6 9 1
要得到以下B表的結果
NO ITM T
1301 1 8
1301 2 2
1302 1 3
1302 2 5
1302 3 6
1302 4 10
1303 1 6
1303 2 9
1303 3 1
請高手幫忙，謝謝！

[Shawn]

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [NO] varchar(100), [T1] varchar(100), [T2] varchar(100), [T3] varchar(100), [T4] varchar(100));
insert #temp
select '1301','8','2',null,null union all
select '1302','3','5','6','10' union all
select '1303','6','9','1',NULL

--SQL:
select [NO], itm=ROW_NUMBER() OVER(PARTITION BY [NO] ORDER BY GETDATE()), T=aa 
from #temp a
UNPIVOT
(aa FOR bb IN([t1],[t2],[t3],[t4])) b
/*
NO	itm	T
1301	1	8
1301	2	2
1302	1	3
1302	2	5
1302	3	6
1302	4	10
1303	1	6
1303	2	9
1303	3	1
*/

[Andy__Huang]

create table #tb(No varchar(10),T1 int,T2 int,T3 int,T4 int)
insert into #tb
select 1301,8,2,null,null
union all select 1302,3,5,6,10
union all select 1303,6,9,1,null

select *
from (
select No,1 as ITM,T1 from #tb
union all select No,2,T2 from #tb
union all select No,3,T3 from #tb
union all select No,4,T4 from #tb
)t
where T1 is not null
order by no,ITM
drop table #tb

/*
1301	1	8
1301	2	2
1302	1	3
1302	2	5
1302	3	6
1302	4	10
1303	1	6
1303	2	9
1303	3	1
*/

[Shawn]

--或者用union all:
SELECT * FROM
(
	select [NO],itm=1,T=T1 from #temp
	UNION ALL
	select [NO],itm=2,T=T2 from #temp
	UNION ALL
	select [NO],itm=3,T=T3 from #temp
	UNION ALL
	select [NO],itm=4,T=T4 from #temp
) t
WHERE T IS NOT NULL
ORDER BY NO, itm

[KeepSayingNo]

这属于行转列的应用，建议楼主直接用unpivot，当然前提是楼主是SQL SERVER2005，否则如果是2000就用union all来做吧