---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-28 17:00:50 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([VC_NO] varchar(3),[VC_PERSON] varchar(4)) insert [tb] select '001','张三' union all select '001','李四' union all select '001','王五' union all select '002','丽丽' union all select '002','张三' --------------开始查询-------------------------- select VC_NO, MAX(case id when 1 then vc_person else '' end) as '001', MAX(case ID when 2 then vc_person else '' end) as '002', MAX(case ID when 3 then vc_person else '' end) as '002' from (select ID=ROW_NUMBER()over(PARTITION by vc_no order by getdate()),* from tb)t group by VC_NO----------------结果---------------------------- /* VC_NO 001 002 002 ----- ---- ---- ---- 001 张三 李四 王五 002 丽丽 张三 (2 行受影响)*/
create table [tb]([VC_NO] varchar(3),[VC_PERSON] varchar(4)) insert [tb] select '001','张三' union all select '001','李四' union all select '001','王五' union all select '002','丽丽' union all select '002','张三'select VC_NO, max(case px when 1 then VC_PERSON else '' end) [人员_1], max(case px when 2 then VC_PERSON else '' end) [人员_2], max(case px when 3 then VC_PERSON else '' end) [人员_3] from ( select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t ) m group by VC_NOdrop table tb /* VC_NO 人员_1 人员_2 人员_3 ----- ---- ---- ---- 001 李四 王五 张三 002 丽丽 张三 (所影响的行数为 2 行) */
create table [tb]([VC_NO] varchar(3),[VC_PERSON] varchar(4)) insert [tb] select '001','张三' union all select '001','李四' union all select '001','王五' union all select '002','丽丽' union all select '002','张三'select VC_NO, max(case px when 1 then VC_PERSON else '' end) [人员_1], max(case px when 2 then VC_PERSON else '' end) [人员_2], max(case px when 3 then VC_PERSON else '' end) [人员_3] from ( select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t ) m group by VC_NO /* VC_NO 人员_1 人员_2 人员_3 ----- ---- ---- ---- 001 李四 王五 张三 002 丽丽 张三 (所影响的行数为 2 行) */declare @sql varchar(8000) set @sql = 'select VC_NO ' select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then VC_PERSON else '''' end) [人员_' + cast(px as varchar) + ']' from (select distinct px from (select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t) m) as a set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t)m group by VC_NO' exec(@sql) /* VC_NO 人员_1 人员_2 人员_3 ----- ---- ---- ---- 001 李四 王五 张三 002 丽丽 张三 */drop table tb
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-28 17:00:50
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([VC_NO] varchar(3),[VC_PERSON] varchar(4))
insert [tb]
select '001','张三' union all
select '001','李四' union all
select '001','王五' union all
select '002','丽丽' union all
select '002','张三'
--------------开始查询--------------------------
select
VC_NO,
MAX(case id when 1 then vc_person else '' end) as '001',
MAX(case ID when 2 then vc_person else '' end) as '002',
MAX(case ID when 3 then vc_person else '' end) as '002'
from
(select ID=ROW_NUMBER()over(PARTITION by vc_no order by getdate()),* from tb)t
group by
VC_NO----------------结果----------------------------
/* VC_NO 001 002 002
----- ---- ---- ----
001 张三 李四 王五
002 丽丽 张三 (2 行受影响)*/
insert [tb]
select '001','张三' union all
select '001','李四' union all
select '001','王五' union all
select '002','丽丽' union all
select '002','张三'select VC_NO,
max(case px when 1 then VC_PERSON else '' end) [人员_1],
max(case px when 2 then VC_PERSON else '' end) [人员_2],
max(case px when 3 then VC_PERSON else '' end) [人员_3]
from
(
select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t
) m
group by VC_NOdrop table tb /*
VC_NO 人员_1 人员_2 人员_3
----- ---- ---- ----
001 李四 王五 张三
002 丽丽 张三 (所影响的行数为 2 行)
*/
insert [tb]
select '001','张三' union all
select '001','李四' union all
select '001','王五' union all
select '002','丽丽' union all
select '002','张三'select VC_NO,
max(case px when 1 then VC_PERSON else '' end) [人员_1],
max(case px when 2 then VC_PERSON else '' end) [人员_2],
max(case px when 3 then VC_PERSON else '' end) [人员_3]
from
(
select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t
) m
group by VC_NO
/*
VC_NO 人员_1 人员_2 人员_3
----- ---- ---- ----
001 李四 王五 张三
002 丽丽 张三 (所影响的行数为 2 行)
*/declare @sql varchar(8000)
set @sql = 'select VC_NO '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then VC_PERSON else '''' end) [人员_' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t) m) as a
set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where VC_NO = t.VC_NO and VC_PERSON < t.VC_PERSON) + 1 from tb t)m group by VC_NO'
exec(@sql)
/*
VC_NO 人员_1 人员_2 人员_3
----- ---- ---- ----
001 李四 王五 张三
002 丽丽 张三
*/drop table tb