求一无限分级加行列转换语句

Question

--部门表（无限级）
Create table #Depment(
id int identity(1,1),
depid int ,
depname varchar(100)
)
insert into #Depment select 0,'A'
insert into #Depment select 0,'B'
insert into #Depment select 0,'C'
insert into #Depment select 1,'A1'
insert into #Depment select 1,'A2'
insert into #Depment select 1,'A3'
insert into #Depment select 2,'B1'
insert into #Depment select 2,'B2'
insert into #Depment select 3,'C1'
insert into #Depment select 5,'B21'
insert into #Depment select 5,'B22'
--部门得分表
Create table #score
(
depname varchar(100),
score int,
scoreDate datetime
)
insert into #score select 'A1',50,'2010-03-05'
insert into #score select 'A1',60,'2010-04-05'
insert into #score select 'A1',30,'2010-05-05'
insert into #score select 'A2',30,'2010-03-05'
insert into #score select 'A2',20,'2010-04-05'
insert into #score select 'A2',50,'2010-05-05'
insert into #score select 'A3',80,'2010-03-05'
insert into #score select 'A3',10,'2010-04-05'
insert into #score select 'A3',40,'2010-05-05'
insert into #score select 'B1',50,'2010-03-05'
insert into #score select 'B1',50,'2010-04-05'
insert into #score select 'B21',25,'2010-03-05'
insert into #score select 'B21',30,'2010-04-05'
insert into #score select 'B22',70,'2010-03-05'
insert into #score select 'B22',25,'2010-04-05'
insert into #score select 'C1',50,'2010-04-05'
--结果　
depname    1003      1004     1005
A           160       90       120
A1          50        60       30
A2          30        20       50
A3          80        10       40
B           145       105      0
B1          50        50       0
B2          95        55       0
B21         25        30       0
B22         70        25       0
C           0         50       0
C1          0         50       0

Answer 1

童鞋，一条select最大就能写8000字符

Answer 2

Create table Depment(
id int identity(1,1),
depid int ,
depname varchar(100)
)
insert into Depment select 0,'A'
insert into Depment select 0,'B'
insert into Depment select 0,'C'
insert into Depment select 1,'A1'
insert into Depment select 1,'A2'
insert into Depment select 1,'A3'
insert into Depment select 2,'B1'
insert into Depment select 2,'B2'
insert into Depment select 3,'C1'
insert into Depment select 5,'B21'
insert into Depment select 5,'B22'
--部门得分表
Create table score
(
depname varchar(100),
score int,
scoreDate datetime
)
insert into score select 'A1',50,'2010-03-05'
insert into score select 'A1',60,'2010-04-05'
insert into score select 'A1',30,'2010-05-05'
insert into score select 'A2',30,'2010-03-05'
insert into score select 'A2',20,'2010-04-05'
insert into score select 'A2',50,'2010-05-05'
insert into score select 'A3',80,'2010-03-05'
insert into score select 'A3',10,'2010-04-05'
insert into score select 'A3',40,'2010-05-05'
insert into score select 'B1',50,'2010-03-05'
insert into score select 'B1',50,'2010-04-05'
insert into score select 'B21',25,'2010-03-05'
insert into score select 'B21',30,'2010-04-05'
insert into score select 'B22',70,'2010-03-05'
insert into score select 'B22',25,'2010-04-05'
insert into score select 'C1',50,'2010-04-05'
GODECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT depname' 
SELECT @sql = @sql + ',SUM(CASE WHEN CONVERT(VARCHAR(4),scoreDate,12) = ''' + scoreDate + ''' THEN score ELSE 0 END) AS [' + scoreDate + ']'
FROM (SELECT DISTINCT CONVERT(VARCHAR(4),scoreDate,12) AS scoreDate FROM score)AS Temp
SELECT @sql = @sql + ' FROM (
SELECT A.depname,B.scoreDate,B.score
FROM Depment A
LEFT JOIN score B ON CHARINDEX(A.depname,B.depname) > 0
)X
GROUP BY depname'--SELECT @sql
EXEC (@sql)--Result
/*
depname 1003 1004 1005
A 160 90 120
A1 50 60 30
A2 30 20 50
A3 80 10 40
B 145 105 0
B1 50 50 0
B2 95 55 0
B21 25 30 0
B22 70 25 0
C 0 50 0
C1 0 50 0
*/

Answer 3

sql2005
--------------------
declare @d nvarchar(max)
select @d=(select distinct convert(varchar(4),scoredate,12) as scoredate from #score for xml auto)
select @d=replace(replace(replace(@d,'"/><_x0023_score scoredate="',','),'<_x0023_score scoredate="',''),'"/>','')
declare @sql nvarchar(max)
select @sql='
select Depname,'+@d+' from
(
select Depname,Score,convert(varchar(4),scoredate,12) as scoredate from #score
) as b
pivot
(
sum(Score) for scoredate in('+@d+')
) as piv'
select @sql

Answer 4

declare @d nvarchar(max)
select @d=(select distinct convert(varchar(4),scoredate,12) as scoredate from #score for xml auto)
select @d=replace(replace(replace(@d,'"/><_x0023_score scoredate="',','),'<_x0023_score scoredate="',''),'"/>','')
declare @sql nvarchar(max)
select @sql='
select Depname,'+@d+' from
(
select Depname,Score,convert(varchar(4),scoredate,12) as scoredate from #score
) as b
pivot
(
sum(Score) for scoredate in('+@d+')
) as piv'
execute sp_executesql @sql

Answer 5

select  s.depname,sum(case sdate when '1003' then s.score else 0 end ) as [1003],
                  sum(case sdate when '1004' then s.score else 0 end ) as [1004],
                  sum(case sdate when '1005' then s.score else 0 end ) as [1005]from #score s join (select distinct substring(convert(varchar(10),scoredate,112),3,4) as sdate ,scoreDate
                      from #score 
                       ) d on s.scoreDate=d.scoreDate
 group by s.depname

Answer 6

看了，很强．但假如score的depname换成是#Depment的id 呢？

Answer 7

IF OBJECT_ID('[Depment]') IS NOT NULL
DROP TABLE [Depment]
GO
Create table  Depment(
id int identity(1,1),
depid int ,
depname varchar(100)
)
insert into  Depment select 0,'A'
insert into  Depment select 0,'B'
insert into  Depment select 0,'C'
insert into  Depment select 1,'A1'
insert into  Depment select 1,'A2'
insert into  Depment select 1,'A3'
insert into  Depment select 2,'B1'
insert into  Depment select 2,'B2'
insert into  Depment select 3,'C1'
insert into  Depment select 8,'B21'
insert into  Depment select 8,'B22'
--部门得分表
IF OBJECT_ID('[score]') IS NOT NULL
DROP TABLE [score]
GO
Create table  score
(
depname varchar(100),
score int,
scoreDate datetime
)
insert into  score select 'A1',50,'2010-03-05'
insert into  score select 'A1',60,'2010-04-05'
insert into  score select 'A1',30,'2010-05-05'
insert into  score select 'A2',30,'2010-03-05'
insert into  score select 'A2',20,'2010-04-05'
insert into  score select 'A2',50,'2010-05-05'
insert into  score select 'A3',80,'2010-03-05'
insert into  score select 'A3',10,'2010-04-05'
insert into  score select 'A3',40,'2010-05-05'
insert into  score select 'B1',50,'2010-03-05'
insert into  score select 'B1',50,'2010-04-05'
insert into  score select 'B21',25,'2010-03-05'
insert into  score select 'B21',30,'2010-04-05'
insert into  score select 'B22',70,'2010-03-05'
insert into  score select 'B22',25,'2010-04-05'
insert into  score select 'C1',50,'2010-04-05'
--select * from score
;with t as
(
select id,depid,depname,isnull([1003],0) [1003],isnull([1004],0) [1004],isnull([1005],0) [1005]
from (
select a.id,a.depid,a.depname,isnull(score,0) score,scoreDate= convert(varchar(4),scoreDate,12) 
from Depment a 
left join score b 
on a.depname=b.depname
) a
pivot(sum(score) for scoreDate in([1003],[1004],[1005])) b
)
,t1 as
(
select *,total1=[1003],total2=[1004],total3=[1005] 
from t a
where not exists(
select 1 from t 
where depid=a.id)
union all
select a.*,total1+a.[1003],total2+a.[1004],total3+a.[1005]  
from t a
join t1 b on a.id=b.depid
)
,t2 as
(
select *,lvl=0,px=cast(id as varbinary(max)) from Depment a
union all
select a.*,lvl+1,cast(px+cast(a.id as varbinary) as varbinary(max))
from Depment a
join t2 b 
on a.depid=b.id
)
select a.depname depname,sum(total1) [1003],sum(total2) [1004],sum(total3) [1005]
from t1 a
join t2 b
on a.id=b.id
group by a.depname
/*
depname 1003 1004 1005
A 160 90 120
A1 100 120 60
A2 60 40 100
A3 160 20 80
B 145 105 0
B1 100 100 0
B2 190 110 0
B21 75 90 0
B22 210 75 0
C 0 50 0
C1 0 100 0
*/

Answer 8

--select * from score
;with t as
(
select id,depid,depname,isnull([1003],0) [1003],isnull([1004],0) [1004],isnull([1005],0) [1005]
from (
select a.id,a.depid,a.depname,isnull(score,0) score,scoreDate= convert(varchar(4),scoreDate,12) 
from Depment a 
left join score b 
on a.depname=b.depname
) a
pivot(sum(score) for scoreDate in([1003],[1004],[1005])) b
)
,t1 as
(
select *,total1=[1003],total2=[1004],total3=[1005] 
from t a
where not exists(
select 1 from t 
where depid=a.id)
union all
select a.*,total1+a.[1003],total2+a.[1004],total3+a.[1005]  
from t a
join t1 b on a.id=b.depid
)
,t3 as
(
select * from t
union all
select id,depid, depname,sum(total1) [1003],sum(total2) [1004],sum(total3) [1005]
from t1 
group by id,depid,depname
having depid=0
)
select depname,max([1003])[1003],max([1004])[1004],max([1005])[1005]
from t3
group by depname
/*
depname 1003 1004 1005
A 160 90 120
A1 50 60 30
A2 30 20 50
A3 80 10 40
B 145 105 0
B1 50 50 0
B2 0 0 0
B21 25 30 0
B22 70 25 0
C 0 50 0
C1 0 50 0
*/

Answer 9

[Quote=引用 10 楼 hycheng163 的回复:]
看了，很强．但假如score的depname换成是#Depment的id 呢？
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT depname' 
SELECT @sql = @sql + ',SUM(CASE WHEN CONVERT(VARCHAR(4),scoreDate,12) = ''' + scoreDate + ''' THEN score ELSE 0 END) AS [' + scoreDate + ']'
FROM (SELECT DISTINCT CONVERT(VARCHAR(4),scoreDate,12) AS scoreDate FROM score)AS Temp
SELECT @sql = @sql + ' FROM (
SELECT A.depname,B.scoreDate,B.score
FROM Depment A
LEFT JOIN (SELECT B.depname,A.score,A.scoreDate FROM score A
INNER JOIN Depment B ON A.id = B.id) B ON CHARINDEX(A.depname,B.depname) > 0)X
GROUP BY depname'--SELECT @sql
EXEC (@sql)

Answer 10

[Quote=引用 10 楼 hycheng163 的回复:]
看了，很强．但假如score的depname换成是#Depment的id 呢？改成下面的:
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT depname' 
SELECT @sql = @sql + ',SUM(CASE WHEN CONVERT(VARCHAR(4),scoreDate,12) = ''' + scoreDate + ''' THEN score ELSE 0 END) AS [' + scoreDate + ']'
FROM (SELECT DISTINCT CONVERT(VARCHAR(4),scoreDate,12) AS scoreDate FROM score)AS Temp
SELECT @sql = @sql + ' FROM (
SELECT A.depname,B.scoreDate,B.score
FROM Depment A
LEFT JOIN (SELECT B.depname,A.score,A.scoreDate FROM score A
INNER JOIN Depment B ON A.id = B.id) B ON CHARINDEX(A.depname,B.depname) > 0
)X
GROUP BY depname'--SELECT @sql
EXEC (@sql)

Answer 11

select  s.depname,sum(case sdate when '1003' then s.score else 0 end ) as [1003],
                  sum(case sdate when '1004' then s.score else 0 end ) as [1004],
                  sum(case sdate when '1005' then s.score else 0 end ) as [1005]from (select d.depname,s.score,s.scoreDate
      from #Depment d join #score s on CHARINDEX(d.depname,s.depname)>0 )s 
      join (select distinct substring(convert(varchar(10),scoredate,112),3,4) as sdate ,scoreDate
                      from #score 
                       ) d on s.scoreDate=d.scoreDate
 group by s.depname
 

Answer 12

我的意思是depname是没有规律的，CHARINDEX(A.depname,B.depname)　这就有问题了．

Answer 13

动态生成月份，参考master..spt_values表用法