有如下表order_cus:
id code
1 1
2 1
2 97
2 15747
2 15876
2 15895
2 15896
3 15901
3 15907
3 15914
3 15915
5 15916
5 15917
5 15918
5 15919
5 15922
7 15923
7 15924
7 15925
7 15926
......有这样的数据,我要变成:
id all_code
1 1
2 1,97,15747,15876,15895,15896
3 15901,15907,15914,15915
......就是按照id排序,然后将id相同的code列用','符号连接起来做为第二列
我试过了中国风,dawugui在网上贴的行列转换的代码,由于我这个列不是固定的,是动态的,而且老大只允许用表变量,不允许用临时表以及永久表来存储中间数据。我不知道怎么写了,这样的TSQL如何写?
这个工作任务今天要做完!各位帮帮忙吧!谢谢了!
id code
1 1
2 1
2 97
2 15747
2 15876
2 15895
2 15896
3 15901
3 15907
3 15914
3 15915
5 15916
5 15917
5 15918
5 15919
5 15922
7 15923
7 15924
7 15925
7 15926
......有这样的数据,我要变成:
id all_code
1 1
2 1,97,15747,15876,15895,15896
3 15901,15907,15914,15915
......就是按照id排序,然后将id相同的code列用','符号连接起来做为第二列
我试过了中国风,dawugui在网上贴的行列转换的代码,由于我这个列不是固定的,是动态的,而且老大只允许用表变量,不允许用临时表以及永久表来存储中间数据。我不知道怎么写了,这样的TSQL如何写?
这个工作任务今天要做完!各位帮帮忙吧!谢谢了!
SELECT id,code=CAST(code as varchar(1000))
INTO #t FROM order_cus
ORDER BY id,code
DECLARE @id int,@code varchar(8000)
UPDATE #t SET
@code = CASE WHEN @id=id THEN @code+ ',' + code ELSE code END,
@id=id,
code=@code
SELECT id,code=MAX(code) FROM #t GROUP BY id
drop table #t
INTO #t FROM order_cus
ORDER BY id,all_code
DECLARE @id int,@all_code varchar(8000)
UPDATE #t SET
@all_code= CASE WHEN @id=id THEN @all_code+ ',' + all_code ELSE all_code END,
@id=id,
all_code=@all_code
SELECT id,all_code=MAX(all_code) FROM #t GROUP BY id
drop table #t
-----------------
DECLARE @a table(id int,code varchar(8000))insert INTO @a
SELECT id,code=CAST(code as varchar(8000))
FROM order_cus
ORDER BY id,codeDECLARE @id int,@code varchar(8000)
UPDATE @a SET
@code = CASE WHEN @id=id THEN @code+ ',' + code ELSE code END,
@id=id,code=@code
SELECT id,code=MAX(code) FROM @a GROUP BY id
(
id int,
code nvarchar(30)
)
insert into U
select
1, '1' union all
select 2 ,'1' union all
select 2 ,'97' union all
select 2 ,'15747' union all
select 2 ,'15876' union all
select 2 ,'15895' union all
select 2 ,'15896' union all
select 3 ,'15901' union all
select 3 ,'15907' union all
select 3 ,'15914' union all
select 3 ,'15915' union all
select 5 ,'15916' union all
select 5 ,'15917' union all
select 5 ,'15918' union all
select 5 ,'15919' union all
select 5 ,'15922' union all
select 7 ,'15923' union all
select 7 ,'15924' union all
select 7 ,'15925' union all
select 7 ,'15926'
create function HU(@id as int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+isnull(code+',','') from U where id=@id
return substring(@s,1,len(@s)-1)
end
go
select id,code=dbo.HU(id) from U group by id
drop table [dbo].[REPORT_MSU]
GOCREATE TABLE [dbo].[REPORT_MSU] (
[M_ID] [int] NULL ,
[REALNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[POST_TIME] [datetime] NULL
) ON [PRIMARY]
GO
/*
用于用户消息报表的姓名字段合并
*/
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000);
--PRINT @id;
SET @r = ''
SELECT TOP 5 @r = @r + ',' + REALNAME
FROM REPORT_MSU
WHERE M_ID=@id
SET @r=@r+' ';
SET @r=REPLACE(REPLACE(@r,',,',''),', ','');
--PRINT @r
RETURN STUFF(@r, 1, 1, '')
END
【上面的脚本块只执行一次】TRUNCATE TABLE REPORT_MSU;
/*表REPORT_MSU结构不能随便改动,因为f_str函数里面的数据查询跟REPORT_MSU是一致的*/
INSERT INTO REPORT_MSU
SELECT C.M_ID,ISNULL(D.REALNAME,''),C.POST_TIME
FROM(
SELECT A.ID AS M_ID, A.POST_TIME, B.RECIVER_USER_ID FROM SYS_MESSAGE A
INNER JOIN SYS_MESSAGE_USER B
ON A.ID=B.MESSAGE_ID
)C INNER JOIN SYS_USER D
ON C.RECIVER_USER_ID=D.[ID] ORDER BY C.POST_TIME DESC;SELECT B.POST_TIME,B.REALNAME AS POST_NAME,B.TITLE,B.CONTENT,A.REALNAME AS RECIVED_NAME
FROM (
SELECT M_ID, REALNAME=ISNULL(dbo.f_str(M_ID),'')
FROM REPORT_MSU
GROUP BY M_ID
)A
INNER JOIN (
SELECT C.[ID],C.TITLE,C.CONTENT,C.POST_TIME,D.REALNAME FROM SYS_MESSAGE C
INNER JOIN SYS_USER D
ON C.POST_USER_ID=D.[ID]
) B
ON A.M_ID=B.ID
creat FUNCTION addstr(@id int)
RETURNS varchar(200) AS
BEGIN
declare @resulstr varchar(200)
set @resulstr=''
SELECT @resulstr=@resulstr+card+',' FROM order_cus WHERE id=@id
RETURN(@resulstr)
ENDselect id,dbo.addstr(id) from order_cus group by id
谢谢,搞定了,sql2000用表变量搞不定,只有用临时表了!
'Data Source="c:\2.xls";
User ID=Admin;Password=;Extended properties=Excel 5.0')...[sheet1$]declare @sql nvarchar(4000)
set @sql=N'select * into tbPolicy1 from openrowset(''Microsoft.JET.OLEDB.4.0'',
'''+@FilePath+''';''Admin'';'''',''select * from tbPolicy'')'
print @sql
exec (@sql)