declare @s nvarchar(4000) set @s='' select @s=@s+' '+col1 from (select col1 from t union seletct col2 from t union select col3 from t union select col4 from t)ttselect stuff(@s,1,1,'')
select distinct col from ( select 字段1 as col from tb union all select 字段2 from tb union all select 字段3 from tb union all select 字段4 from tb) t
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(2),[col4] varchar(2)) insert [tb] select 'a','b','c','d' union all select 'a','b','cc','dd'
---查询--- declare @line varchar(100) select @line=isnull(@line,'')+col+' ' from( select distinct col from ( select col1 as col from tb union all select col2 from tb union all select col3 from tb union all select col4 from tb) t) tt select @line as col---结果--- col ---------------------------------------------------------------------------------------------------- a b c cc d dd (所影响的行数为 1 行)
--> By dobear_0922(小熊) 2009-02-13 11:03:50 --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(2),[字段4] varchar(2)) insert [tb] select 'a','b','c','d' union all select 'a','b','cc','dd'select ltrim( (select ' '+col from ( select 字段1 as col from tb union select 字段2 from tb union select 字段3 from tb union select 字段4 from tb ) T for xml path('')) )/*-------------------- a b c cc d dd(1 行受影响)*/drop table [tb]
哈是不是我说错了,字段1 字段2 字段3 字段4 a b c d a b cc dd 实现dataset:字段1:a 字段2:b 字段3:c 字段4:d 字段5:cc 字段6:dd
if object_id('tb')is not null drop table tb go create table tb(COL1 varchar(10),COL2 VARCHAR(10),COL3 VARCHAR(10),COL4 VARCHAR(10)) INSERT TB SELECT 'a', 'b' , 'c' , 'd' INSERT TB SELECT 'a', 'b' , 'cc' , 'dd' declare @s nvarchar(4000) set @s='' select @s=@s+' '+'字段'+ltrim((select count(*) from (select col1 from tb union select col2 from tb union select col3 from tb union select col4 from tb) a where a.col1<=tt.col1))+': '+col1 from (select col1 from tb union select col2 from tb union select col3 from tb union select col4 from tb)tt select stuff(@s,1,1,'') /*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 字段1: a 字段2: b 字段3: c 字段4: cc 字段5: d 字段6: dd(影響 1 個資料列) */
set @s=''
select @s=@s+' '+col1
from (select col1 from t
union
seletct col2 from t
union
select col3 from t
union
select col4 from t)ttselect stuff(@s,1,1,'')
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
from (
select 字段1 as col from tb
union all
select 字段2 from tb
union all
select 字段3 from tb
union all
select 字段4 from tb) t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(1),[col2] varchar(1),[col3] varchar(2),[col4] varchar(2))
insert [tb]
select 'a','b','c','d' union all
select 'a','b','cc','dd'
---查询---
declare @line varchar(100)
select @line=isnull(@line,'')+col+' ' from(
select distinct col
from (
select col1 as col from tb
union all
select col2 from tb
union all
select col3 from tb
union all
select col4 from tb) t) tt
select @line as col---结果---
col
----------------------------------------------------------------------------------------------------
a b c cc d dd (所影响的行数为 1 行)
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(2),[字段4] varchar(2))
insert [tb]
select 'a','b','c','d' union all
select 'a','b','cc','dd'select ltrim(
(select ' '+col from
( select 字段1 as col from tb union select 字段2 from tb
union select 字段3 from tb union select 字段4 from tb
) T
for xml path(''))
)/*--------------------
a b c cc d dd(1 行受影响)*/drop table [tb]
a b c d
a b cc dd
实现dataset:字段1:a 字段2:b 字段3:c 字段4:d 字段5:cc 字段6:dd
go
create table tb(COL1 varchar(10),COL2 VARCHAR(10),COL3 VARCHAR(10),COL4 VARCHAR(10))
INSERT TB SELECT 'a', 'b' , 'c' , 'd'
INSERT TB SELECT 'a', 'b' , 'cc' , 'dd'
declare @s nvarchar(4000)
set @s=''
select @s=@s+' '+'字段'+ltrim((select count(*) from (select col1 from tb
union
select col2 from tb
union
select col3 from tb
union
select col4 from tb) a where a.col1<=tt.col1))+': '+col1
from (select col1 from tb
union
select col2 from tb
union
select col3 from tb
union
select col4 from tb)tt
select stuff(@s,1,1,'')
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
字段1: a 字段2: b 字段3: c 字段4: cc 字段5: d 字段6: dd(影響 1 個資料列)
*/
1 123 a b
1 123 aa bb
是想实现:
线路 车号 司机1 售票员1 司机2 售票员2
1 123 a b aa bb