sql 合并列

[aplesen]

数据库是sql server2005的，
有表 Table_20100830 如下：
tid tname tcount1 tcount2
1 奔驰 1 2
2 宝马 2 3
3 福特 5 NULL
4 本田 NULL 5
我需要得到如下结果：
tid tname tcount
1 奔驰 1
1 奔驰 2
2 宝马 2
2 宝马 3
3 福特 5
4 本田 5
请问这个sql语句该如何写，小弟在线等

[aplesen]

怎么没人回答呢？

[PxxxP]

没看明白什么规律？

[水族杰纶]

if not object_id('tb') is null
	drop table tb
Go
Create table tb([tid] int,[tname] nvarchar(2),[tcount1] int,[tcount2] int)
Insert tb
select 1,N'奔驰',1,2 union all
select 2,N'宝马',2,3 union all
select 3,N'福特',5,null union all
select 4,N'本田',null,5
Go
Select [tid],[tname],[tcount1] from tb where [tcount1] is not null
union all
Select [tid],[tname],[tcount2] from tb where [tcount2] is not null
order by tid
/*
tid         tname tcount1
----------- ----- -----------
1           奔驰    1
1           奔驰    2
2           宝马    3
2           宝马    2
3           福特    5
4           本田    5

(6 個資料列受到影響)
*/

[DEATH64]

SELECT tid ,tname ,tcount
FROM #MyTable
UNPIVOT
(
tcount FOR BH IN ([tcount1],[tcount2])
) Y

没测试,看看行不行.

[sinpoal]

Select [tid],[tname],[tcount1] from tb where [tcount1] is not null
union all
Select [tid],[tname],[tcount2] from tb where [tcount2] is not null
order by tid

顶

[aplesen]

[Quote=引用 5 楼 sinpoal 的回复:]
SQL code

Select [tid],[tname],[tcount1] from tb where [tcount1] is not null
union all
Select [tid],[tname],[tcount2] from tb where [tcount2] is not null
order by tid


顶
[/Quote]
顶