sqlserver 2005 交叉表和层次关系查询

sqlserver2005已经方便提供交叉表和层次关系查询,下面分别举例说明:

--交叉表查询
create table sales(
id int,
area nvarchar(20),
product_id nvarchar(4)
)
go

insert into sales
select 1,'aa','a001'
 union all
select 2,'bb','b001'
union all
select 3,'cc','c001'
union all
select 4,'cc','c002'
go
select * from sales
--原表记录：
---------------
1 aa a001
2 bb b001
3 cc c001
4 cc c002

--查询
declare @s varchar(8000)
declare @sql varchar(8000)
set @s=''
set @sql=''
select @s = isnull(@s + ',','')+ ltrim(area)
from (select distinct area from sales) a
set @s = right(@s,len(@s)-1)

--print @s

set @sql = 'SELECT id,'+@s+'
FROM
sales
PIVOT
( COUNT (product_id)
FOR area IN ('+@s+' )
)AS unpvt'
--print @sql

exec(@sql)
--行列转换结果
id  aa  bb  cc
-------------------
1 1 0 0
2 0 1 0
3 0 0 1
4 0 0 1

--输出的sql
SELECT id,aa,bb,cc
FROM
sales
PIVOT
( COUNT (product_id)
FOR area IN (aa,bb,cc)
)AS unpvt

---层次关系查询
create table EmployeeTbl(
id int identity,
name varchar(20),
pid int
)
go
insert into EmployeeTbl
(name,pid)
select 'root',0
union all
select 'root001',1
union all
select 'root002',1
go
insert into EmployeeTbl
(name,pid)
select 'root001_a',2
union all
select 'root001_b',2
go

select * from EmployeeTbl
go

-------------------------------

原表中数据:

id name         pid

------------------------------

1 root             0
2 root001       1
3 root002       1
4 root001_a   2
5 root001_b   2

 

--查询root001的上级层次关系

with c as (
select * from EmployeeTbl where id = 2
union all
select a.* from EmployeeTbl a
join c on a.id = c.pid
)
select * from c order by id asc

--------------------------------------

结果:

1 root        0
2 root001  1

 

--查询root001的下级层次关系

with c as (
select * from EmployeeTbl where name = 'root001'
union all
select a.* from EmployeeTbl a
join c on a.id = c.pid
)
select * from c

--------------------------------------

结果:

2 root001 1
4 root001_a 2
5 root001_b 2

 

本文纯属http://blog.csdn.net/yown 原创!! 欢迎转载!!