create table ceshi
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','鲍双喜')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','王永明')接着写。结果biaoshi name
1 鲍双喜 杨八斤 王永明
2 曹飞宇
3 高玲玲 邵林海
4 杨可婷 刘继肖
5 郑开寅 蒋路仙
6 朱秀琴 徐丽静
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','鲍双喜')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','王永明')接着写。结果biaoshi name
1 鲍双喜 杨八斤 王永明
2 曹飞宇
3 高玲玲 邵林海
4 杨可婷 刘继肖
5 郑开寅 蒋路仙
6 朱秀琴 徐丽静
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','鲍双喜')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','王永明')
SELECT DISTINCT biaoshi ,
name=LTRIM((SELECT ' '+name FROM ceshi WHERE biaoshi=t.biaoshi FOR XML PATH('')))
FROM ceshi t
/*
biaoshi name
----------- --------------------------------
1 鲍双喜 杨八斤 王永明
2 曹飞宇
3 高玲玲 邵林海
4 杨可婷 刘继肖
5 郑开寅 蒋路仙
6 朱秀琴 徐丽静(6 行受影响)
*/
create table ceshi
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','鲍双喜')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','王永明')
select biaoshi, name= stuff((select ' '+name
from ceshi
where a.biaoshi=biaoshi for xml path('')),1,1,'')
from ceshi a
group by biaoshibiaoshi name
1 鲍双喜 杨八斤 王永明
2 曹飞宇
3 高玲玲 邵林海
4 杨可婷 刘继肖
5 郑开寅 蒋路仙
6 朱秀琴 徐丽静
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','鲍双喜')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','王永明')
SELECT DISTINCT biaoshi ,
name=LTRIM((SELECT ' '+name FROM ceshi WHERE biaoshi=t.biaoshi FOR XML PATH('')))
FROM ceshi t服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'XML' 附近有语法错误。
INSERT INTO TT
SELECT '10001','支票',2000
UNION ALL
SELECT '10001','预留款',1000
UNION ALL
SELECT '10001','现金',800
UNION ALL
SELECT '10002','挂账',1000
UNION ALL
SELECT '10002','现金',800create function dbo.FC_Str(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+type+':'+cast(billMoney as varchar) from TT where id=@id
return stuff(@str,1,1,'')
endselect id,sum(billMoney) payedMoney,dbo.FC_Str(id) from TT group by id
(
id int identity(1,1)not null,
biaoshi int not null,
name varchar(50) not null
)insert into ceshi values('1','王永明')
insert into ceshi values('3','高玲玲')
insert into ceshi values('4','杨可婷')
insert into ceshi values('5','郑开寅')
insert into ceshi values('6','朱秀琴')
insert into ceshi values('1','杨八斤')
insert into ceshi values('2','曹飞宇')
insert into ceshi values('3','邵林海')
insert into ceshi values('4','刘继肖')
insert into ceshi values('5','蒋路仙')
insert into ceshi values('6','徐丽静')
insert into ceshi values('1','鲍双喜')
--UDF的方式:
IF OBJECT_ID('f_Get_Str')>0
DROP FUNCTION f_Get_Str
GO
CREATE FUNCTION f_Get_Str(@biaoshi int)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @r NVARCHAR(50)
SELECT @r=ISNULL(@r+' ','')+name
FROM ceshi
WHERE biaoshi=@biaoshi
RETURN @r
END
GOSELECT DISTINCT biaoshi ,dbo.f_Get_Str(biaoshi) name
FROM ceshi
/*
biaoshi name
----------- --------------------------------
1 鲍双喜 杨八斤 王永明
2 曹飞宇
3 高玲玲 邵林海
4 杨可婷 刘继肖
5 郑开寅 蒋路仙
6 朱秀琴 徐丽静(6 行受影响)
*/DROP TABLE ceshi
--TestData
CREATE TABLE #DataTest
(
[id] int,[dept] varchar(100)
)
INSERT INTO #DataTest
VALUES(1,'1-A')
INSERT INTO #DataTest
VALUES(1,'1-B')
INSERT INTO #DataTest
VALUES(1,'1-C')
INSERT INTO #DataTest
VALUES(2,'2-A')
INSERT INTO #DataTest
VALUES(2,'2-D')
INSERT INTO #DataTest
VALUES(3,'3-A')--結果
/************************
1 1-A、1-B、1-C
2 2-A、2-D
3 3-A
**************************/
CREATE TABLE #dataTemp([id] int ,[dept] VARCHAR(100),CNT INT IDENTITY(1,1))
INSERT INTO #dataTemp
SELECT DISTINCT
[id],[dept]
FROM #DataTest
--------------------CNT切り替え------------------
SELECT A.[id],A.[dept],A.CNT-B.CNT+1 C INTO #Ndept FROM #dataTemp A
JOIN (SELECT [id] ,MIN(CNT) CNT FROM #dataTemp GROUP BY [id]) B
ON A.[id]=B.[id]CREATE TABLE #ALLdept([id] BIGINT,[dept] NVARCHAR(1000))
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
SET @SQL1=''
SET @SQL2=''
SET @SQL1=@SQL1+' insert into #ALLdept SELECT [id], RTRIM('
SELECT @SQL1=@SQL1+'ISNULL(''、''+MAX(['+CONVERT(VARCHAR,C)+']),'''')+' FROM(SELECT DISTINCT C FROM #Ndept )A
SET @SQL1=@SQL1+''' '') AS dept FROM( '
SET @SQL2=@SQL2+'SELECT [id] '
SELECT @SQL2=@SQL2+',CASE WHEN C='+CONVERT(VARCHAR,C)+' THEN dept END ['+CONVERT(VARCHAR,C)+'] '
FROM(SELECT DISTINCT C FROM #Ndept )A
SET @SQL2=@SQL2+'FROM #Ndept)A GROUP BY [id] '
PRINT @SQL1+@SQL2
EXEC(@SQL1+@SQL2)
SELECT [id],right([dept],len([dept])-1)
FROM #ALLdeptDROP TABLE #DataTest
drop table #Ndept
drop table #dataTemp
drop table #ALLdept
你自己改吧
name=LTRIM((SELECT ' '+name FROM ceshi WHERE biaoshi=t.biaoshi FOR XML PATH('')))
FROM ceshi t服务器: 消息 170,级别 15,状态 1,行 2
第 2 行: 'XML' 附近有语法错误。
这个是怎么回事
SQL2000的用6楼的代码。
DROP FUNCTION f_Get_Str
GO
CREATE FUNCTION f_Get_Str(@biaoshi int)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @r NVARCHAR(50)
SELECT @r=ISNULL(@r+' ','')+name
FROM ceshi
WHERE biaoshi=@biaoshi
RETURN @r
END
GOSELECT DISTINCT biaoshi ,dbo.f_Get_Str(biaoshi) name
FROM ceshi
DROP FUNCTION f_Get_Str
GO
CREATE FUNCTION f_Get_Str(@biaoshi int)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @r NVARCHAR(50)
SELECT @r=ISNULL(@r+'','')+name
FROM ceshi
WHERE biaoshi=@biaoshi
RETURN @r
END
GOSELECT DISTINCT biaoshi ,dbo.f_Get_Str(biaoshi) name
FROM ceshi