34,590
社区成员
发帖
与我相关
我的任务
分享
--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 #t
DECLARE @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 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
*/
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
*/
--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