你是想这样?--2005 create table ym (col1 int, col2 int, col3 varchar(3))insert into ym select 1, 1, 'A' union all select 1, 1, 'B' union all select 1, 2, 'C' union all select 1, 3, 'D' union all select 1, 3, 'E' select a.col1,a.col2, stuff((select ','+col3 from ym b where b.col1=a.col1 and b.col2=a.col2 for xml path('')),1,1,'') 'col3' from ym a group by a.col1,a.col2
create table ym (col1 int,col2 varchar(3))
insert into ym select 3, 'A' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E'如果是通过col1取TOP 3的话希望的结果是: B D A
create table ym (col1 int,col2 varchar(3))
insert into ym select 3, 'A' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E';WITH cte AS ( SELECT TOP 3 * FROM ym ORDER BY col1 DESC ) select DISTINCT stuff((SELECT ','+col2 from cte b
for xml path('')),1,1,'') 'col2' from cte a/* col2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- B,D,A */
这是不要逗号的create table ym (col1 int,col2 varchar(3))
insert into ym select 3, 'A' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E';WITH cte AS ( SELECT TOP 3 * FROM ym ORDER BY col1 DESC ) select DISTINCT stuff((SELECT ' '+col2 from cte b
for xml path('')),1,1,'') 'col2' from cte a/* col2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- B D A */
加了个变量@n用来控制top多少。准备下班。。 create table ym (col1 int,col2 varchar(3))
insert into ym select 3, 'A' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E' DECLARE @n INT SET @n=4 --这里设变量控制返回多少个数 ;WITH cte AS ( SELECT TOP (@n) * FROM ym ORDER BY col1 DESC ) select DISTINCT stuff((SELECT ' '+col2 from cte b
for xml path('')),1,1,'') 'col2' from cte a/* col2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- B D A C */
版主的方法好强大呀,B D A C这个字符串的结果是对的。 但我想把B D A C作为一个4个字段的一条记录插入另一张表再进行其他处理,这个该如何处理呀, for xml path之类的实在是不懂呀 实际使用时N初步计划为50,谢谢。
那直接行转列就可以了,没必要用for xml path--drop table ym create table ym (col1 int,col2 varchar(3))
insert into ym select 3, 'A' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E' union all select 5, 'B' union all select 2, 'C' union all select 4, 'D' union all select 1, 'E'IF OBJECT_ID('tempdb..#t','u')IS NOT NULL DROP TABLE #tDECLARE @n INT SET @n=7 --这里设变量控制返回多少个数SELECT TOP (@n) *INTO #T FROM ym ORDER BY col1 DESC declare @s nvarchar(max) set @s='' Select @s=@s+','+quotename(N'列'+CAST(ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)AS VARCHAR(200)))+'=max(case when [col2]='+quotename(col2,'''')+' then [col2] else null end)' from #T SET @s=SUBSTRING(@s,2,LEN(@s)) exec('select '+@s+' from #T') /* 列1 列2 列3 列4 列5 列6 列7 ---- ---- ---- ---- ---- ---- ---- B B D D A C C */
create table ym
(col1 int, col2 int, col3 varchar(3))insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'
select a.col1,a.col2,
stuff((select ','+col3 from ym b
where b.col1=a.col1 and b.col2=a.col2
for xml path('')),1,1,'') 'col3'
from ym a
group by a.col1,a.col2
(col1 int,col2 varchar(3))
insert into ym
select 3, 'A' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E'如果是通过col1取TOP 3的话希望的结果是:
B D A
(col1 int,col2 varchar(3))
insert into ym
select 3, 'A' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E';WITH cte AS (
SELECT TOP 3 *
FROM ym
ORDER BY col1 DESC )
select DISTINCT
stuff((SELECT ','+col2 from cte b
for xml path('')),1,1,'') 'col2'
from cte a/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B,D,A
*/
(col1 int,col2 varchar(3))
insert into ym
select 3, 'A' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E';WITH cte AS (
SELECT TOP 3 *
FROM ym
ORDER BY col1 DESC )
select DISTINCT
stuff((SELECT ' '+col2 from cte b
for xml path('')),1,1,'') 'col2'
from cte a/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A
*/
create table ym
(col1 int,col2 varchar(3))
insert into ym
select 3, 'A' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E'
DECLARE @n INT
SET @n=4 --这里设变量控制返回多少个数
;WITH cte AS (
SELECT TOP (@n) *
FROM ym
ORDER BY col1 DESC )
select DISTINCT
stuff((SELECT ' '+col2 from cte b
for xml path('')),1,1,'') 'col2'
from cte a/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A C
*/
但我想把B D A C作为一个4个字段的一条记录插入另一张表再进行其他处理,这个该如何处理呀, for xml path之类的实在是不懂呀
实际使用时N初步计划为50,谢谢。
create table ym
(col1 int,col2 varchar(3))
insert into ym
select 3, 'A' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E' union all
select 5, 'B' union all
select 2, 'C' union all
select 4, 'D' union all
select 1, 'E'IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #tDECLARE @n INT
SET @n=7 --这里设变量控制返回多少个数SELECT TOP (@n) *INTO #T
FROM ym
ORDER BY col1 DESC
declare @s nvarchar(max)
set @s=''
Select @s=@s+','+quotename(N'列'+CAST(ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)AS VARCHAR(200)))+'=max(case when [col2]='+quotename(col2,'''')+' then [col2] else null end)'
from #T
SET @s=SUBSTRING(@s,2,LEN(@s))
exec('select '+@s+' from #T')
/*
列1 列2 列3 列4 列5 列6 列7
---- ---- ---- ---- ---- ---- ----
B B D D A C C
*/