摘要:sql - for xml raw
如果你的客戶要求你做出的顯示方式如下，一般我們在寫程式時，我們會如何做 ?
一定是用個While loop 迴圈去把DataTable內的資料一個一個串起來，然後再丟
到Datagrid 或是 Repaeater. 其實有個很簡單的做法，一個SQL就搞定了
If your customer requre the data display must show like below. How will you do ?
I think I will use While loop to do it. And bind with Datagrid or Repeater ...etc.
Now there is a nice sql script can help us do it easier. Please see my sample.
【Target】
【解決方式(solution)】
SELECT Row_number()
OVER (
ORDER BY verb) AS Column1,
TB2.contain AS Column2
FROM (SELECT *
FROM (SELECT 'I' AS Suject,
'LOVE' AS Verb,
'U' AS Noun) A) TB1
OUTER apply (SELECT Contain=(SELECT *
FROM ((SELECT *
FROM (SELECT 'LOVE' AS
Verb,
'Promise' AS Do,
'Girl Friend' AS
Target,
'Happiness' AS
Finally) A
WHERE A.verb = TB1.verb))B
FOR xml raw)) TB2
【Result】
這樣的XML格式，再從後端將欄位值讀取出後加工就完成了，對於開發者來說
真的是非常的省時又省力的語法，CP值非常的高，下篇文章我會分享在.NET如何
讀取XML
And U can coding a function to read this value of Column and show it on web page.
It's very useful. I like it. N next article I will share how to read this type of column in .NET.