求:
列 pid ,003, 004, 005, 006, 007, 008, 011, 012
值: A 003001,004002,005008,006001,007001,008001,011001,012001
............
B 003001,004002,005009,006001,007001,008002,011001,012001
距离数据
id pid subid
003 A 003001
004 A 004002
005 A 005008
006 A 006001
007 A 007001
008 A 008001
011 A 011001
012 A 012001
003 B 003001
004 B 004002
005 B 005008
006 B 006001
007 B 007001
008 B 008002
011 B 011001
012 B 012001
003 C 003001
004 C 004002
005 C 005009
006 C 006001
007 C 007001
008 C 008002
010 C 010003
011 C 011001
012 C 012001
UNION ALL
SELECT ID='004',PID,[004] FROM TB
.....转成用动态的吧
select @sql = isnull(@sql + ' union all ' , '' ) + ' select id , [pid] = ' + quotename(Name , '''') + ' , [subid] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'id' and ID = object_id('tb')
order by colid asc
exec(@sql + ' order by pid ')
FROM 表
PIVOT
(
MAX(subid)
FOR ID IN ([003],[004],[005],[006],[007],[008],[011],[012])
) AS pvt
ORDER BY pvt.pid;该方法,只能在Sql2005以上版本才OK
declare @s varchar(8000)
select @s= isnull(@s+',','')+'['+ id+']' from #a group by id
exec('select * from #a pivot (max(subid) for id in ('+@s+'))b')
----------------结果--------------
/*
pid 003 004 005 006 007 008 010 011 012
A 003001 004002 005008 006001 007001 008001 NULL 011001 012001
B 003001 004002 005008 006001 007001 008002 NULL 011001 012001
C 003001 004002 005009 006001 007001 008002 010003 011001 012001
*/
--======= 每天都在进步,却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-10-16 10:10:17=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: tb
if object_id('tb') is not null drop table tb
create table tb (id varchar(3),pid varchar(1),subid varchar(6))
insert into tb
select '003','A','003001' union all
select '004','A','004002' union all
select '005','A','005008' union all
select '006','A','006001' union all
select '007','A','007001' union all
select '008','A','008001' union all
select '011','A','011001' union all
select '012','A','012001' union all
select '003','B','003001' union all
select '004','B','004002' union all
select '005','B','005008' union all
select '006','B','006001' union all
select '007','B','007001' union all
select '008','B','008002' union all
select '011','B','011001' union all
select '012','B','012001' union all
select '003','C','003001' union all
select '004','C','004002' union all
select '005','C','005009' union all
select '006','C','006001' union all
select '007','C','007001' union all
select '008','C','008002' union all
select '010','C','010003' union all
select '011','C','011001' union all
select '012','C','012001'----------------查询------------
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select id , [pid] = ' + quotename(Name , '''') + ' , [subid] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'id' and ID = object_id('tb')
order by colid asc
exec(@sql + ' order by pid ')
----------------结果--------------
/*
id pid subid
003 pid A
004 pid A
005 pid A
006 pid A
007 pid A
008 pid A
011 pid A
012 pid A
003 pid B
004 pid B
005 pid B
006 pid B
007 pid B
008 pid B
011 pid B
012 pid B
003 pid C
004 pid C
005 pid C
006 pid C
007 pid C
008 pid C
010 pid C
011 pid C
012 pid C
003 subid 003001
004 subid 004002
005 subid 005008
006 subid 006001
007 subid 007001
008 subid 008001
011 subid 011001
012 subid 012001
003 subid 003001
004 subid 004002
005 subid 005008
006 subid 006001
007 subid 007001
008 subid 008002
011 subid 011001
012 subid 012001
003 subid 003001
004 subid 004002
005 subid 005009
006 subid 006001
007 subid 007001
008 subid 008002
010 subid 010003
011 subid 011001
012 subid 012001
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 11:35:32
-- 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]([pid] varchar(1),[003] varchar(6),[004] varchar(6),[005] varchar(6),[006] varchar(6),[007] varchar(6),[008] varchar(6),[011] varchar(6),[012] varchar(6))
insert [tb]
select 'A','003001','004002','005008','006001','007001','008001','011001','012001' union all
select 'B','003001','004002','005009','006001','007001','008002','011001','012001'
--------------开始查询--------------------------
select pid , [id] = '003' , [subid] = [003] from tb
union all
select pid , [id] = '004' , [subid] = [004] from tb
union all
select pid , [id] = '005' , [subid] = [005] from tb
union all
select pid , [id] = '006' , [subid] = [006] from tb
union all
select pid , [id] = '007' , [subid] = [007] from tb
union all
select pid , [id] = '008' , [subid] = [008] from tb
union all
select pid , [id] = '011' , [subid] = [011] from tb
union all
select pid , [id] = '012' , [subid] = [012]
from tb
order by pid ----------------结果----------------------------
/* pid id subid
---- ---- ------
A 003 003001
A 004 004002
A 005 005008
A 006 006001
A 007 007001
A 008 008001
A 011 011001
A 012 012001
B 012 012001
B 011 011001
B 008 008002
B 007 007001
B 006 006001
B 005 005009
B 004 004002
B 003 003001(16 行受影响)*/
go
create table tb (id varchar(3),pid varchar(1),subid varchar(6))
insert into tb
select '003','A','003001' union all
select '004','A','004002' union all
select '005','A','005008' union all
select '006','A','006001' union all
select '007','A','007001' union all
select '008','A','008001' union all
select '011','A','011001' union all
select '012','A','012001' union all
select '003','B','003001' union all
select '004','B','004002' union all
select '005','B','005008' union all
select '006','B','006001' union all
select '007','B','007001' union all
select '008','B','008002' union all
select '011','B','011001' union all
select '012','B','012001' union all
select '003','C','003001' union all
select '004','C','004002' union all
select '005','C','005009' union all
select '006','C','006001' union all
select '007','C','007001' union all
select '008','C','008002' union all
select '010','C','010003' union all
select '011','C','011001' union all
select '012','C','012001'declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when id='''+id+''' then subid end) as ['+id+']'
from (select distinct id from tb) t
set @sql='select pid,'+@sql+' from tb group by pid'
exec (@sql)/**
pid 003 004 005 006 007 008 010 011 012
---- ------ ------ ------ ------ ------ ------ ------ ------ ------
A 003001 004002 005008 006001 007001 008001 NULL 011001 012001
B 003001 004002 005008 006001 007001 008002 NULL 011001 012001
C 003001 004002 005009 006001 007001 008002 010003 011001 012001
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
**/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 13:01:12
-- 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] varchar(3),[pid] varchar(1),[subid] varchar(6))
insert [tb]
select '003','A','003001' union all
select '004','A','004002' union all
select '005','A','005008' union all
select '006','A','006001' union all
select '007','A','007001' union all
select '008','A','008001' union all
select '011','A','011001' union all
select '012','A','012001' union all
select '003','B','003001' union all
select '004','B','004002' union all
select '005','B','005008' union all
select '006','B','006001' union all
select '007','B','007001' union all
select '008','B','008002' union all
select '011','B','011001' union all
select '012','B','012001' union all
select '003','C','003001' union all
select '004','C','004002' union all
select '005','C','005009' union all
select '006','C','006001' union all
select '007','C','007001' union all
select '008','C','008002' union all
select '010','C','010003' union all
select '011','C','011001' union all
select '012','C','012001'
--------------开始查询--------------------------
select
pid,
max(case when id='003' then subid end) as [003],
max(case when id='004' then subid end) as [004],
max(case when id='005' then subid end) as [005],
max(case when id='006' then subid end) as [006],
max(case when id='007' then subid end) as [007],
max(case when id='008' then subid end) as [008],
max(case when id='010' then subid end) as [010],
max(case when id='011' then subid end) as [011],
max(case when id='012' then subid end) as [012]
from
tb
group by
pid
----------------结果----------------------------
/* pid 003 004 005 006 007 008 010 011 012
---- ------ ------ ------ ------ ------ ------ ------ ------ ------
A 003001 004002 005008 006001 007001 008001 NULL 011001 012001
B 003001 004002 005008 006001 007001 008002 NULL 011001 012001
C 003001 004002 005009 006001 007001 008002 010003 011001 012001
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/
select @s= isnull(@s+',','')+'['+ id+']' from #a group by id
exec('select * from #a pivot (max(subid) for id in ('+@s+'))b')