表A
id name
1 a
1 b
1 c
2 d
3 e
3 f
想显示出来的结果如下
id name1 name2 name3
1 a b c
3 e f
2 d
就是想按ID的个数排序(1三个,3两个,1一个)然后显示出ID的值,
能用一条SQL实现吗?
id name
1 a
1 b
1 c
2 d
3 e
3 f
想显示出来的结果如下
id name1 name2 name3
1 a b c
3 e f
2 d
就是想按ID的个数排序(1三个,3两个,1一个)然后显示出ID的值,
能用一条SQL实现吗?
id name
1 a
1 b
1 c
2 d
3 e
3 f
想显示出来的结果如下
id name1 name2 name3
1 a b c
3 e f
2 d
就是想按ID的个数排序(1三个,3两个,2一个)然后显示出ID的值,
能用一条SQL实现吗?
id name
1 a
1 b
1 c
3 e
3 f
2 d
也可以,也是按ID的个数排序(1三个,3两个,2一个),当然上面的更好一些,谢谢
insert into tb values(1, 'a')
insert into tb values(1, 'b')
insert into tb values(1, 'c')
insert into tb values(2, 'd')
insert into tb values(3, 'e')
insert into tb values(3, 'f')
godeclare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then name else '' '' end) [name' + cast(px as varchar) + ']'
from (select distinct px from (select px=(select count(1) from tb where id=a.id and name<a.name)+1 , * from tb a) t) as m
set @sql = @sql + ' from (select px=(select count(1) from tb where id=a.id and name<a.name)+1 , * from tb a) t group by id'
exec(@sql) drop table tb/*
id name1 name2 name3
----------- ---------- ---------- ----------
1 a b c
2 d
3 e f
*/
insert into tb values(1, 'a')
insert into tb values(1, 'b')
insert into tb values(1, 'c')
insert into tb values(2, 'd')
insert into tb values(3, 'e')
insert into tb values(3, 'f')
goselect id ,
max(case when px = 1 then name else '' end) 'name1',
max(case when px = 2 then name else '' end) 'name2',
max(case when px = 3 then name else '' end) 'name3'
from
(
select px=(select count(1) from tb where id=a.id and name<a.name)+1 , * from tb a
) t
group by iddrop table tb/*
id name1 name2 name3
----------- ---------- ---------- ----------
1 a b c
2 d
3 e f (所影响的行数为 3 行)
*/
insert into tb values(1,'a ')
insert into tb values(1,'b ')
insert into tb values(1,'c ')
insert into tb values(2,'d ')
insert into tb values(3,'e ')
insert into tb values(3,'f ')
go select id,
max(case when px = 1 then name else ' ' end) 'name1 ',
max(case when px = 2 then name else ' ' end) 'name2 ',
max(case when px = 3 then name else ' ' end) 'name3 '
from
(
select px=(select count(1) from tb where id=a.id and name <a.name)+1 , * from tb a
) t
group by id
order by count(id) desc
drop table tb
order by charindex(convert(varchar(2),px),'1,3,2')
insert into tb values(1, 'a ')
insert into tb values(1, 'b ')
insert into tb values(1, 'c ')
insert into tb values(2, 'd ')
insert into tb values(3, 'e ')
insert into tb values(3, 'f ')
go
create function getfull(@id int)
returns varchar(100)
as
begin
declare @name varchar(100)
select @name=isnull(@name+name,name) from tb where id=@id
return @name
endselect distinct(id),dbo.getfull(id) from tb
from
(select id,count(*) count from table_name) as a
left join table_name as b
on a.id=b.id
order by count desc
id name
1 a
1 b
1 c
2 d
3 e
3 f
想显示出来的结果如下
id name1 name2 name3
1 a b c
3 e f
2 d
就是想按ID的个数排序(1三个,3两个,1一个)然后显示出ID的值,
能用一条SQL实现吗?
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+name from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go