我有一個包含如下資料的表：
========= ============ ========= ==============
| num | module-id | page-no | txt |
========= ============ ========= ==============
| 3177564 | 1 | 1 |text string 1 |
--------- ------------ --------- --------------
| 3177564 | 1 | 2 |text string 2 |
--------- ------------ --------- --------------
| 3177564 | 1 | 3 |text string 3 |
--------- ------------ --------- --------------
| 3177564 | 2 | 1 |text string 1 |
--------- ------------ --------- --------------
| 3177564 | 2 | 2 |text string 2 |
--------- ------------ --------- --------------
| 3177564 | 3 | 1 |text string 1 |
--------- ------------ --------- --------------
| 3177564 | 3 | 2 |text string 2 |
--------- ------------ --------- --------------
| 3177564 | 3 | 3 |text string 3 |
--------- ------------ --------- --------------
然后我可以使用 PIVOT 函式生成以下內容：
========= ============ ============== ============== ==============
| num | module-id | page-no-1 | page-no-2 | page-no-3 |
========= ============ ============== ============== ==============
| 3177564 | 1 |text string 1 |text string 2 |text string 3 |
--------- ------------ -------------- -------------- --------------
| 3177564 | 2 |text string 1 |text string 2 |NULL |
--------- ------------ -------------- -------------- --------------
| 3177564 | 3 |text string 1 |text string2 |text string 3 |
--------- ------------ -------------- -------------- --------------
用于生成樞軸的代碼是：
SELECT *
FROM
(
SELECT
j.num
,jpad.[module-id]
,jpad.[page-no]
,jpad.txt
FROM
job j
LEFT OUTER JOIN jobpad jpad ON jpad.num = j.num
WHERE
(j.num IN
('3177564'))
AND (jpad.[page-no] IN (1,2,3,4,5,6,7,8,9,10)) a
PIVOT
(
MAX(a.txt) FOR a.[page-no] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) p
ORDER BY num
這很好，但是，如果可能的話，我想做的是按照以下示例連接資料：
請注意，p-1、p-2 等是指頁碼。
========= ========================================================= ============= =============
| num | module-id-1 | module-id-2 | module-id-3 |
========= ========================================================= ============= =============
| 3177564 |p-1 text string 1 & p-2 text string 2 & p-3 text string 3|per module 1 |per module 1 |
--------- --------------------------------------------------------- ------------- -------------
我一直在思考這個問題，但不能完全在我的腦海中得到它。
我目前使用的是 SQL Server 2014。
uj5u.com熱心網友回復：
要獲得您所追求的第一列：
; -- see sqlblog.org/cte
WITH src AS
(
SELECT num, [module-id], txt = STUFF(
(SELECT CONCAT(' & ', 'p-', [page-no], ' ', txt)
FROM dbo.StuffAndThings WHERE num = s.num
AND [module-id] = s.[module-id]
FOR XML PATH(''),
TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 3, '')
FROM dbo.StuffAndThings AS s
WHERE [page-no] = 1
)
SELECT num,
[module-id-1] = [1],
[module-id-2] = [2],
[module-id-3] = [3]
FROM src PIVOT
(MAX(txt) FOR [module-id] IN ([1],[2],[3])) AS p;
- 示例db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/466601.html
上一篇：使用交叉應用同時顯示兩列