|
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,'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,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 原创!! 欢迎转载!!
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
