SQL variable concatenation with xml

Question

have the following table coming from this query:

SELECT 
     [Document], 
     [Description], 
     [Value], 
FROM [DocDetails]

Document    Description      Value      Line No_
  120         First Row       100              1
  120         Second Row      0                2
  120         Third row       0                3
  120         Fourth row      0                4 
  120         fifth row       0                5
  120         sixth row       203              6
  120         seventh row     256              7
  120         eighth row      259              8
  120         ninth row       0                9
  120         tenth row       0                10
  120         eleventh row    0                11

I need to concatenate the description according to the value. I would need such result:

Document    Description                                              Value
  120         First Row;second row;Third row;Fourth row;fifth row     100
  120         sixth row                                               203
  120         seventh row                                             256
  120         eighth row;ninth row;tenth row;eleventh row             259

I tried the following:

SELECT 
  [Document], 
  All_Descriptions = STUFF(
         (SELECT ';' + Description AS [text()]
          FROM [DocDetails] D1
          WHERE D1.[Document] = D2.[Document] 
          FOR XML PATH('')),1,1,'')
          FROM [DocDetails] D2
              GROUP BY D2.[Document]

As I don't have a variable that specifies the order I am not able to concatenate properly ( the code above concatenates everything but that's not what I want). Also if I group by value I am not getting the desired result. How can I tell SQL to basically "concatenate the row with an amount with all the following having value 0"

Thanks for your help!

Answer 1

This is another solution that works with SQL 2008 too.

DECLARE @DocDetails TABLE( [Document] int, [Description] varchar(20), [Value] int, [Line_No] int )
INSERT INTO @DocDetails VALUES
    (120,'First Row',100,1),
    (120,'Second Row',0,2),
    (120,'Third row',0,3),
    (120,'Fourth row',0,4),
    (120,'fifth row',0,5),
    (120,'sixth row',203,6),
    (120,'seventh row',256,7),
    (120,'eighth row',259,8),
    (120,'ninth row',0,9),
    (120,'tenth row',0,10),
    (120,'eleventh row',0,11),
    (121,'eleventh row',0,11)

;WITH 
LinesWithValue AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [Document] ORDER BY Line_No ) RN
    FROM @DocDetails 
    WHERE Value > 0 
)
,LinesWithNext AS (
    SELECT L.*, L1.Line_No Next_Line_No 
    FROM LinesWithValue L 
    LEFT JOIN LinesWithValue L1 ON L.RN + 1 = L1.RN AND L.[Document] = L1.[Document]
 )
,NewTable AS (SELECT 
    B.Document, 
    B.Description, 
    CASE B.Value WHEN 0 THEN A.Value ELSE B.Value END Value, 
    B.Line_No 
    FROM LinesWithNext A 
    FULL JOIN @DocDetails B ON A.[Document] = B.[Document] AND ( ( B.Line_No >= A.Line_No ) AND ( A.Next_Line_No IS NULL OR  B.Line_No < A.Next_Line_No ) )
 )
 SELECT 
   [Document], 
   [Value],
   All_Descriptions = STUFF(
         (SELECT ';' + Description AS [text()]
         FROM NewTable D1
         WHERE D1.[Document] = D2.[Document] AND D1.[Value] = D2.[Value]
         FOR XML PATH('')) , 1, 1, '')
 FROM NewTable D2
 GROUP BY D2.[Document], [Value]

Answer 2

Here's a way....

declare @table table(Document int,[Description] varchar(64), [Value] int, Line_No int)
insert into @table
values
(120,'First Row',100,1),
(120,'Second Row',0,2),
(120,'Third row',0,3),
(120,'Fourth row',0,4),
(120,'fifth row',0,5),
(120,'sixth row',203,6),
(120,'seventh row',256,7),
(120,'eighth row',259,8),
(120,'ninth row',0,9),
(120,'tenth row',0,10),
(120,'eleventh row',0,11)

--Find the end / anchor line which to stop the concatenation later
;with cte as(
select
    t.Document
    ,t.[Value]
    ,t.Description
    ,t.Line_No
    ,Parent_Line = isnull(min(t2.Line_No) - 1, (select max(Line_No) from @table))
from
    @table t
    full join
    @table t2 on t2.Document = t.Document 
    and t2.Line_No > t.Line_No
    and t2.Value <> 0
where
    t.Document is not null
group by
    t.Document
    ,t.[Value]
    ,t.Line_No
    ,t.Description),

--Do the concatenation of the Description
cte2 as (
select
    Document
    ,value
    ,All_Descriptions = STUFF((
        SELECT ',' + t2.Description
        FROM cte t2
        WHERE t.Parent_Line = t2.Parent_Line
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    ,Parent_Line
from
    cte t)

--Get max [Value] for uniqueness
select
    Document
    ,All_Descriptions
    ,[Value] = max([Value])
from 
    cte2
group by
    Document
    ,All_Descriptions
order by
    max([Value])