Is there a way to take this data, pivot it and then combine it into a single string..?-CodePudding

I have a table that contains data as follows:


 ========= ============ ========= ============== 
|   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 |
 --------- ------------ --------- --------------

I can then use the PIVOT function to produce the following:


 ========= ============ ============== ============== ============== 
|   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 |
 --------- ------------ -------------- -------------- --------------

The code used to produce the pivot is:

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

This is great however, what I would like to do is concatenate the data as per the following example if at all possible:

Please note that p-1, p-2 etc. refers to the page numbers.


 ========= ========================================================= ============= ============= 
|   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 |
 --------- --------------------------------------------------------- ------------- -------------

I've been pondering this for a while now but can't quite get it right in my head.

I am currently on SQL Server 2014.

CodePudding user response：

To get the first column you're after:

; -- 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;

Example db<>fiddle