3

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!

Improve this question
7
  • What is your sort column ? Commented Aug 28, 2017 at 13:43
  • whats the real data look like? An attempt with this data would probably not work in your real environment. Currently, you have no real way of ordering this data--we need more realistic sample. @sagi LAG and LEAD is only available 2012 onward. Commented Aug 28, 2017 at 13:48
  • I am sorting by the variable [Line No_] , I have updated the table above. @Sagi I have read already about LAG/LEAD but it is unfortunately not supported by sql server 2008 Commented Aug 28, 2017 at 13:49
  • Well Nic, I don't see a [Line_No] in the sample data... Commented Aug 28, 2017 at 13:50
  • Oh didn't see the version :P Commented Aug 28, 2017 at 13:50

2 Answers 2

Reset to default
2

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]
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much for your help! This also works great!
1

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])
Improve this answer

1 Comment

Thank you very much!! this worked perfectly for me! Thanks a lot for your help much appreciated

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.