Flatten Rows Using FOR XML PATH 1

[briangriffin]

Source data looks like this:

PurchaseOrderNumber   POLineNumber    TextSequence     TextLine
13472                    1                1            This
13472                    1                2            Is
13472                    1                3            Text

Found this code online and modified it, but I'm getting multiple records:

select 
    
b.PurchaseOrderID,
b.LineID,
stuff((select ',' + a.TextLine AS [text()]
                        
from 
Livendb.dbo.MmPoText a

where
a.PurchaseOrderID = b.PurchaseOrderID
and a.LineID = b.LineID
FOR XML PATH('')), 1, 1, '' )
as CombinedTest

from Livendb.dbo.MmPoText b
 
where PurchaseOrderID = 13472

PurchaseOrderNumber  POLineNumber        CombinedText
13472                    1                  This Is Text
13472                    1                  This Is Text
13472                    1                  This Is Text

Can't quite figure out how to only return one record for each PO/LineItem combination. Thanks in advance.

 

[fredericofonseca]

try

select b.PurchaseOrderID
     , b.LineID
     , stuff((select ',' + a.TextLine AS [text()]
              from Livendb.dbo.MmPoText a 
              where a.PurchaseOrderID = b.PurchaseOrderID
              and a.LineID = b.LineID
              FOR XML PATH('')
             ), 1, 1, '' ) as CombinedTest

from Livendb.dbo.MmPoText b
where PurchaseOrderID = 13472 
[b]group by b.PurchaseOrderID
       , b.LineID[/b]

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[briangriffin]

Perfect, thank you.