將資料表中某欄位值串接成字串

今天在逛網站時，突然看到有人在介紹SQL Server新的T-SQL指令PIVOT。大致上就瞭解及試了一下這種PIVOT的用途。在試過語法後，發現要使用PIVOT語法時，必須明確地知道所需要的橫向欄位有多少。

例如：


-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

這個來自MSDN上的PIVOT範例。執行結果如下：

由上面的範例來看，PIVOT可以將原本是直行的查詢結果，轉換成橫向的查詢結果。不過，這樣的語法使用上就是必須事先知道所要的橫向欄位有哪些。如果一開始不確定PIVOT中要的橫向欄位有哪些的話，那該如何是好。

最好的方式就是這些橫向欄位是可以由另外一份清單來取得。不過，在PIVOT中它無法再支援子查詢的方式，也就是不能寫成：

PIVOT

(

AVG(StandardCost)

FOR DaysToManufacture IN ( select * FROM xxxx )

) AS PivotTable;

可能的作法就是要自已動態組合SQL指令，再來執行了。執行的方式可以用：

execute sp_executesql @SQLCmd

如何組合出動態PIVOT橫向欄位

在網路上找到一篇有關如何將資料表中的資料組成一組串列的方式，不過，這篇文章中的組合方式好像有點問題。那篇文章中作者使用到COALESCE()這個T-SQL指令。COALESCE()指令的用法是將兩個參數中不為NULL的那個傳回。最後組出來的字串中，第一個字串前面也會多了一個','。所以我就改寫了一下語法，不使用COALESCE()，而是用case when的方式。

create table #TestColListTable
(
ColName nvarchar(50)
)

insert into #TestColListTable VALUES ('Menu_01')
insert into #TestColListTable VALUES ('Menu_02')
insert into #TestColListTable VALUES ('Menu_03')
insert into #TestColListTable VALUES ('Menu_04')

declare @colList nvarchar(800)
set @colList = ''

-- Functin 1
select @colList =
case
when len( @colList ) > 0 then
@colList + ',[' + ColName + ']'
else
'[' + ColName + ']'
end
from #TestColListTable

print 'Function 1:'
print @colList

-- Function 2
set @ColList = '';
select @colList = COALESCE( @ColList + ',[' + ColName + ']', '[' + ColName + ']')
from #TestColListTable

print 'Function 2:'
print @colList

drop table #TestColListTable

執行結果如下：Function2是網路上的語法，可以看出它會多了一個','。使用Function1的方式就可以正確地組出所需的PIVOT橫向欄位，可以套用到PIVOT指令中來使用了。