一條金典的SQL查詢，結合了 for xml、Pivot、ISNULL 應用示例

首先看看要實作的資料和要實作的結果圖：

use master

create    table tb

(

項目名 nvarchar(300),

專業名 nvarchar(300),

檔案名 nvarchar(300)

);

insert into tb values ('項目A', '油','油-1'),('項目A','油','油-2'),('項目A','水','水-1'),('項目B ','油','油-3'),('項目B ','油','油-4'),('項目B ','水','水-2')

--方法1:

select 項目名,

stuff(

(

select ','+檔案名 from tb where 項目名=b.項目名 and 專業名='油' for XML path('')

),1,1,'')油,

stuff(

(

select ','+檔案名 from tb where 項目名=b.項目名 and 專業名='水' for XML path('')

),1,1,'')水

 from tb B group by 項目名

-- 方法2:

select 項目名,

stuff(

(

select ','+(case 專業名 when '油' then 檔案名 end)  from tb where 項目名=b.項目名 for XML path('')  

),1,1,'')油,stuff(

(

select ','+(case 專業名 when '水' then 檔案名 end)  from tb where 項目名=b.項目名 for XML path('')  

),1,1,'')水 from tb B group by 項目名

--由于專業名是動态的,不可能隻有油和水這兩種專業,是以要動态拼接,最後也有兩種方法

-- 方法1:

declare @str nvarchar(500),@pro nvarchar(300),@sql nvarchar(max)='select 項目名'

set @str=(select 專業名+',' from tb group  by 專業名 for xml path(''))

while(charindex(',',@str)>0)  

  begin  

   select @pro=substring(@str,1,charindex(',',@str)-1);

   select @str=substring(@str,charindex(',',@str)+1,LEN(@str)); 

   set @[email protected]+',stuff(

(

select '',''+檔案名 from tb where 項目名=b.項目名 and 專業名='''[email protected]+''' for XML path('''')

),1,1,'''')'[email protected]+'';

  end  

set @[email protected]+' from tb B group by 項目名';  

--print @sql

EXECUTE(@sql)

-- 方法1:

declare @sql varchar(1000)

select @sql=isnull(@sql+'],[','')+專業名 from tb group by 專業名

set @sql='['[email protected]+']'

exec('

;with t as (select 項目名,專業名,stuff((select '',''+檔案名 from tb where 項目名=t.項目名 and 專業名=t.專業名 for xml path('''')),1,1,'''') 檔案名 from tb t group by 項目名,專業名,檔案名)

select * from (select * from t) a pivot (max(檔案名) for 專業名 in ('[email protected]+'))b')

整個過程，最後一個動态拼接的方法是最棒的，值得學習、研究的，裡面用到了For Xml 、IsNull 列資料合并到字元串中以及Pivot行轉列。

下面的東西隻是為了加深印象，和标題無關

create table #tt

(

name nvarchar(30),

subj  nvarchar(30),

score int

)

insert into #tt values ('張三','國文',80),('張三','數學',90),('張三','實體',85),('李四','國文',85),('李四','實體',82),('李四','英語',90),('李四','政治',70),('王五','英語',90)

select name,

max(case subj when '國文' then score else 0 END) as '國文',

max(case subj when '數學' then score else 0 END) as '數學',

max(case subj when '實體' then score else 0 END) as '實體',

max(case subj when '英語' then score else 0 END) as '英語',

max(case subj when '實體' then score else 0 END) as '政治'

from #tt group by name

declare @cmdText varchar(8000)

set @cmdText='select name, '

select @[email protected]+' sum(case subj when'''+subj+'''Then score else 0 end) as '''+subj+''','+char(10) from (select Distinct subj from #tt with(nolock)) T

print @cmdText--發現多一個逗号下面把逗号去掉

set @cmdText=left(@cmdText,len(@cmdText)-2)--去掉逗号

set @[email protected]+'from #tt group by name'

print @cmdText

exec(@cmdText)

----------------解決方案二-----------------------

declare @str nvarchar(100)

--方式1--

--select  @str=isnull(@str + '],[' , '') + ltrim([subj]) from #tt group by [subj]

--set @str ='[' + @str + ']' 

--方式2--

select @str= isnull(@str,'')+'['+  subj +'],' from #tt  group by [subj]

set @str=left(@str,len(@str)-1);

exec ('select * from #tt pivot 

(

max(score) for subj in ('[email protected]+')

)cc')