declare @s varchar(300)
set @s = ''
select @s = @s + name + ',' from yourtable
select left(@s, len(@s)-1)
set @s = ''
select @s = @s + name + ',' from yourtable
select left(@s, len(@s)-1)
解决方案 »
- 这么简单的函数怎么写不 明白呢????????????
- '2004-11-2 下午 8:37:31'
- 急!无论用哪种方式Windows还是sqlserver身份验证都无法登陆本地数据库???
- 自动增量问题
- SQL数据库问题,请教高手!!!
- 为什么win98安装不上SQL Server 2000 安装服务器版 请各位大虾帮忙!!!
- 这两句insert效果是一样的。。为什么有些人还要加N呢?有什么好处?
- 急~~~!问个关于SQL更新和添加方面的问题
- 求助:Microsoft SQL Server 2000服务不能启动
- java的jdbc和sqlserver的查询引擎
- 兩個字段的值組成表記錄的唯一標誌這樣的表的主鍵和索引怎麼設置???
- 很菜的问题,快帮帮我!!
仔细看看我的例子吧,不懂在问我,记得要给分哦。 8)ALTER PROCEDURE db_no_itcs_port (@tstart_date datetime,
@tend_date datetime,
@tcorp_id nvarchar(10),
@tdep_id nvarchar(400), --事业部范围
@ttable_name NVarChar(20) output,
@errcode int output) --国际付税资金计划报表
AS
BEGIN
SET @errcode=0
DECLARE @err INT
DECLARE @TempDate Datetime --定义日期变量
DECLARE @valueadded_tax_rate numeric(15,6) --定义增值税率
DECLARE @l int
DECLARE @l1 int
DECLARE @p1 int
DECLARE @corp_name nvarchar(20) --公司名称
DECLARE @dep_id nvarchar(4) --事业部ID
DECLARE @dep_name nvarchar(20) --事业部名称
DECLARE @LIFNR nvarchar(10) --口岸
DECLARE @port_name nvarchar(30) --口岸名称
DECLARE @BEDNR nvarchar(20) --PR编号
DECLARE @EBELN nvarchar(10) --PO号
DECLARE @EBELP nvarchar(5) --PO行项目号
DECLARE @MATNR nvarchar(18) --物料编号
DECLARE @MENGE numeric(13,3) --订单数量
DECLARE @amount numeric(13,3) --剩余数量
DECLARE @PRICE numeric(11,2) --单价
DECLARE @waers nvarchar(5) --货币名称代码币制
DECLARE @SFDAT Datetime --应付日期
DECLARE @IHRAN Datetime --报价日期(发货日期)
DECLARE @KBETR numeric(11,2) --R3关税税率
DECLARE @tax_rate numeric(15,6) --OMS关税税率
DECLARE @purchase_grp_name nvarchar(10) --采购组名称
DECLARE @tax_amt numeric(15,6) --关税税款
DECLARE @valueadded_tax numeric(15,6) --增值税税款
DECLARE @tax_count numeric(15,6) --税款合计
DECLARE @texchange_rate numeric(11,2) --汇率
DECLARE @rtable_name NVarChar(20)
DECLARE @sqlbuf nvarchar(4000),@SqlResult nvarchar(4000),@sql nvarchar(4000)
DECLARE @m int,@i int
DECLARE @tport_name nvarchar (30)
DECLARE @FieldName nvarchar(100)
DECLARE @RC int
DECLARE @errcode1 int
EXEC @RC = [db_no_itcs] @tstart_date, @tend_date, @tcorp_id, @tdep_id, @rtable_name OUTPUT , @errcode1 OUTPUT
IF @errcode1 <> 0
BEGIN
SET @errcode = -1
RETURN
END
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP_db_no_itcs_port
SET @sql='SELECT distinct corp_name,dep_name,SFDAT INTO ##TEMP_db_no_itcs_port FROM '+ @rtable_name +' '
SET @sql=@sql + 'ORDER BY corp_name,dep_name,SFDAT '
--print @Sql
EXECUTE sp_executesql @Sql SET @sql='DECLARE cursor_port CURSOR forward_only static read_only FOR SELECT DISTINCT port_name FROM '+ @rtable_name +' '
--print @Sql
EXECUTE sp_executesql @Sql
OPEN cursor_port
SET @m=@@CURSOR_ROWS
SET @i=0
WHILE @i<@m
BEGIN
FETCH cursor_port INTO @tport_name
SET @FieldName = '[' + @tport_name +']'
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP1_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP1_db_no_itcs_port
SET @sql='SELECT distinct corp_name,dep_name,SFDAT,ISNULL(sum(tax_count),0) AS ' + @FieldName + ' '
SET @sql=@sql+'INTO ##TEMP1_db_no_itcs_port FROM '+ @rtable_name +' '
SET @sql=@sql+'WHERE port_name='''+@tport_name+''' '
SET @sql=@sql+'GROUP BY corp_name,dep_name,SFDAT '
--PRINT @sql
EXECUTE sp_executesql @Sql --加入到资金预估全集中
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP2_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP2_db_no_itcs_port
SET @sql='SELECT T.*,ISNULL(T1.'+@FieldName+',0) AS ''' + @tport_name + ''' INTO ##TEMP2_db_no_itcs_port '
SET @sql=@sql+'FROM ##TEMP_db_no_itcs_port T LEFT OUTER JOIN ##TEMP1_db_no_itcs_port T1 ON T.corp_name=T1.corp_name AND T.dep_name=T1.dep_name AND T.SFDAT=T1.SFDAT '
--PRINT @sql
EXECUTE sp_executesql @Sql --临时中转,避免已存在的中间临时表
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP_db_no_itcs_port
SET @SqlResult = 'SELECT * INTO ##TEMP_db_no_itcs_port FROM ##TEMP2_db_no_itcs_port'
EXECUTE sp_executesql @SqlResult
SET @i=@i+1
END
CLOSE cursor_port
DEALLOCATE cursor_port IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP1_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP1_db_no_itcs_port
SET @sql='SELECT distinct corp_name,dep_name,SFDAT,ISNULL(sum(tax_count),0) AS subsum '
SET @sql=@sql+'INTO ##TEMP1_db_no_itcs_port FROM '+ @rtable_name +' '
SET @sql=@sql+'GROUP BY corp_name,dep_name,SFDAT '
--PRINT @sql
EXECUTE sp_executesql @Sql --加入到资金预估全集中
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP2_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP2_db_no_itcs_port
SET @sql='SELECT T.*,ISNULL(T1.subsum,0) AS subsum INTO ##TEMP2_db_no_itcs_port '
SET @sql=@sql+'FROM ##TEMP_db_no_itcs_port T LEFT OUTER JOIN ##TEMP1_db_no_itcs_port T1 ON T.corp_name=T1.corp_name AND T.dep_name=T1.dep_name AND T.SFDAT=T1.SFDAT '
--PRINT @sql
EXECUTE sp_executesql @Sql --临时中转,避免已存在的中间临时表
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP_db_no_itcs_port
SET @SqlResult = 'SELECT * INTO ##TEMP_db_no_itcs_port FROM ##TEMP2_db_no_itcs_port'
EXECUTE sp_executesql @SqlResult DECLARE @current_value nvarchar(100)
EXECUTE db_get_seq 'noitcsptt_seq', 6, @current_value OUTPUT, @err OUTPUT
SET @ttable_name = '##noitcsptt' + @current_value
PRINT @ttable_name
IF @@ERROR <> 0 OR @err <> 0
BEGIN
SET @errcode = -1
RETURN
END
SET @SqlResult = 'IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = ''' + @ttable_name + ''' AND type = ''U'') '
SET @SqlResult = @SqlResult + 'DROP TABLE '+@ttable_name
EXECUTE sp_executesql @SqlResult
SET @SqlResult = 'SELECT * INTO '+@ttable_name+' FROM ##TEMP_db_no_itcs_port '
EXECUTE sp_executesql @SqlResult SET @SqlResult = 'IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = ''' + @rtable_name + ''' AND type = ''U'') '
SET @SqlResult = @SqlResult + 'DROP TABLE '+@rtable_name
EXECUTE sp_executesql @SqlResult
SET @sqlbuf = 'SELECT* from ' + @ttable_name + ' '
EXECUTE sp_executesql @sqlbuf
--删除中间过程用到的全局临时表(3个)
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP1_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP1_db_no_itcs_port
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP2_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP2_db_no_itcs_port
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##TEMP_db_no_itcs_port' AND type = 'U')
DROP TABLE ##TEMP_db_no_itcs_port/*
DECLARE @RC int
DECLARE @tstart_date datetime
DECLARE @tend_date datetime
DECLARE @tcorp_id nvarchar(10)
DECLARE @tdep_id nvarchar(400)
DECLARE @ttable_name nvarchar(20)
DECLARE @errcode int
SET @tstart_date='2003-01-01'
SET @tend_date='2003-07-01'
SET @tcorp_id='0001'
SET @tdep_id='''0117'',''0116'',''1111'''
EXEC @RC = [db_no_itcs_port] @tstart_date, @tend_date, @tcorp_id, @tdep_id, @ttable_name OUTPUT , @errcode OUTPUT
*/
END
set @s = ''
select @s = @s + rtrim(ltrim('人名')) + ',' from table1
select left(@s, len(@s)-1)
----------- ----------
1 cool
1 nice
1 wow
2 cool
2 wow
3 cool
3 nice
4 nice
This is a result:
id value
----------- -------------
1 cool,nice,wow,
2 cool,wow,
3 cool,nice,
4 nice,实现方法:
set nocount on
declare @YourResultTable table (id int, value varchar(10))
insert @YourResultTable values(1, 'cool')
insert @YourResultTable values(1, 'nice')
insert @YourResultTable values(1, 'wow')
insert @YourResultTable values(2, 'cool')
insert @YourResultTable values(2, 'wow')
insert @YourResultTable values(3, 'cool')
insert @YourResultTable values(3, 'nice')
insert @YourResultTable values(4, 'nice')
select * from @YourResultTable
declare @z varchar(100),
@Step int,
@q int
select @Step = 0,
@q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int)
insert @ProcessTable
select id, value, 0
from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' ,
@q = @q + case @q when id then 0 else 1 end,
@Step = ProcessStep = @Step + 1
from @ProcessTableselect id,
value
from @ProcessTable t
join (select max(ProcessStep) MaxStep
from @ProcessTable
group by id) x
on ProcessStep = MaxStepset nocount off
RETURNS varchar(2000) AS
BEGIN declare @return varchar(2000)select @return=isnull(@return,'')+isnull(人名,'')+',' from yourtable where sort_id=@parm
return (@return)
ENDselect dbo.uf_xd([人名]) from yourtable
declare @s varchar(8000)
set @s=''
declare a cursor for
select name from people
open a
fetch next from a into @y
while @@fetch_status=0
begin
set @s=@s+',name'
end
close a
deallocate a
exec('select id'''+@s+''''from people')
declare @str varchar(300)
set @s = ''
select @str = @str +','+ name from tb
select right(@str, len(@str)-1)你可以将它做成一个自定义函数,这样就可以在查询中直接使用
set @s = ''
改为:
set @str = ''--自定义函数
create function megstr(@bm varchar(10)
returns varchar(300)
as
begin
declare @str varchar(300)
set @str = ''
select @str = @str +','+ name from tb where bm=@bm
select right(@str, len(@str)-1)
return(@str)
end
go
使用:
select bm,megst(bm) as name from tb