declare @tb1 table(id int,name varchar(50)) insert into @tb1 select 1,'张三' insert into @tb1 select 1,'李四' insert into @tb1 select 1,'王五' insert into @tb1 select 2,'赵二' insert into @tb1 select 2,'钱成' insert into @tb1 select 2,'孙七'--2005 select id, max(case when pid=1 then name end) as name1, max(case when pid=2 then name end) as name2, max(case when pid=3 then name end) as name3 from ( select *,row_number() over(partition by id order by name) as pid from @tb1) t group by id--2000 select id, max(case when pid=1 then name end) as name1, max(case when pid=2 then name end) as name2, max(case when pid=3 then name end) as name3 from ( select *,(select count(*) from @tb1 where id=t.id and name<=t.name) as pid from @tb1 t)t group by id
id name1 name2 name3 1 李四 王五 张三 2 钱成 孙七 赵二
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2009-12-30 13:21:32 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb1] if object_id('[tb1]') is not null drop table [tb1] go create table [tb1]([id] int,[name] varchar(4)) insert [tb1] select 1,'张三' union all select 1,'李四' union all select 1,'王五' union all select 2,'赵二' union all select 2,'钱成' union all select 2,'孙七' --------------开始查询--------------------------select * from [tb1]select IDD=IDENTITY(INT,1,1),* INTO #T from [TB1]SELECT ID, MAX(CASE WHEN (IDD-1)%3=0 THEN NAME ELSE '' END)AS NAME1, MAX(CASE WHEN (IDD-1)%3=1 THEN NAME ELSE '' END)AS NAME2, MAX(CASE WHEN (IDD-1)%3=2 THEN NAME ELSE '' END)AS NAME3 FROM #T GROUP BY ID,(IDD-1)/3 ----------------结果---------------------------- /* ID NAME1 NAME2 NAME3 ----------- ----- ----- ----- 1 张三 李四 王五 2 赵二 钱成 孙七(所影响的行数为 2 行) */ --DROP TABLE #T
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-12-30 13:18:02 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[name] varchar(4)) insert [tb] select 1,'张三' union all select 1,'李四' union all select 1,'王五' union all select 2,'赵二' union all select 2,'钱成' union all select 2,'孙七' --------------开始查询-------------------------- select id, max(case id0 when 1 then name else '' end) as name1, max(case id0 when 2 then name else '' end) as name2, max(case id0 when 3 then name else '' end) as name3 from (select id0=row_number()over(partition by id order by id),* from tb)t group by id ----------------结果---------------------------- /* id name1 name2 name3 ----------- ----- ----- ----- 1 张三 李四 王五 2 赵二 钱成 孙七(2 行受影响)*/
insert into @tb1 select 1,'张三'
insert into @tb1 select 1,'李四'
insert into @tb1 select 1,'王五'
insert into @tb1 select 2,'赵二'
insert into @tb1 select 2,'钱成'
insert into @tb1 select 2,'孙七'--2005
select id,
max(case when pid=1 then name end) as name1,
max(case when pid=2 then name end) as name2,
max(case when pid=3 then name end) as name3
from (
select *,row_number() over(partition by id order by name) as pid
from @tb1) t
group by id--2000
select id,
max(case when pid=1 then name end) as name1,
max(case when pid=2 then name end) as name2,
max(case when pid=3 then name end) as name3
from (
select *,(select count(*) from @tb1 where id=t.id and name<=t.name) as pid
from @tb1 t)t
group by id
1 李四 王五 张三
2 钱成 孙七 赵二
-- Author :SQL77(只为思齐老)
-- Date :2009-12-30 13:21:32
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[name] varchar(4))
insert [tb1]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵二' union all
select 2,'钱成' union all
select 2,'孙七'
--------------开始查询--------------------------select * from [tb1]select IDD=IDENTITY(INT,1,1),* INTO #T from [TB1]SELECT ID,
MAX(CASE WHEN (IDD-1)%3=0 THEN NAME ELSE '' END)AS NAME1,
MAX(CASE WHEN (IDD-1)%3=1 THEN NAME ELSE '' END)AS NAME2,
MAX(CASE WHEN (IDD-1)%3=2 THEN NAME ELSE '' END)AS NAME3
FROM #T GROUP BY ID,(IDD-1)/3
----------------结果----------------------------
/*
ID NAME1 NAME2 NAME3
----------- ----- ----- -----
1 张三 李四 王五
2 赵二 钱成 孙七(所影响的行数为 2 行)
*/
--DROP TABLE #T
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-30 13:18:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵二' union all
select 2,'钱成' union all
select 2,'孙七'
--------------开始查询--------------------------
select
id,
max(case id0 when 1 then name else '' end) as name1,
max(case id0 when 2 then name else '' end) as name2,
max(case id0 when 3 then name else '' end) as name3
from
(select id0=row_number()over(partition by id order by id),* from tb)t
group by
id
----------------结果----------------------------
/* id name1 name2 name3
----------- ----- ----- -----
1 张三 李四 王五
2 赵二 钱成 孙七(2 行受影响)*/