Field concatenation

[wbodger]

If I have this dataset

SalesOrder SalesOrderLine Expr1 Comment
045702	1	2	ITEM 10 (SHIP VIA: GROUND)(PL119853)         
045702	3	4	ITEM 10 (SHIP VIA: GROUND)(PL119855)         
045702	5	6	ITEM 10 (SHIP VIA: GROUND) (PL120208)        
045702	7	8	ITEM 10 (SHIP VIA: GROUND) (PL120210)        
045702	9	10	ITEM 10 (SHIP VIA: GROUND)(PL120209)         
045702	11	12	ITEM 10 REL 180 (SHIP VIA: GROUND)(PL120967) 
045702	13	14	ITEM 10 REL 161 (SHIP VIA: GROUND)(PL121224) 
045702	15	16	ITEM 10 REL 160 (SHIP VIA: GROUND)(PL121491) 
045702	18	19	ITEM 10 REL 190 (SHIP VIA: GROUND)(PL121845) 
045702	20	21	ITEM 10 REL 200 (SHIP VIA:FEDEX              
045702	20	22	P1)(PL121624)                                
045702	23	24	ITEM 10 REL 204 (SHIP VIA: UPS               
045702	23	25	OVERNIGHT)(PL121774)

How could I combine the last four lines into two lines? i.e.

SalesOrder SalesOrderLine Expr1 Comment
045702	1	2	ITEM 10 (SHIP VIA: GROUND)(PL119853)         
045702	3	4	ITEM 10 (SHIP VIA: GROUND)(PL119855)         
045702	5	6	ITEM 10 (SHIP VIA: GROUND) (PL120208)        
045702	7	8	ITEM 10 (SHIP VIA: GROUND) (PL120210)        
045702	9	10	ITEM 10 (SHIP VIA: GROUND)(PL120209)         
045702	11	12	ITEM 10 REL 180 (SHIP VIA: GROUND)(PL120967) 
045702	13	14	ITEM 10 REL 161 (SHIP VIA: GROUND)(PL121224) 
045702	15	16	ITEM 10 REL 160 (SHIP VIA: GROUND)(PL121491) 
045702	18	19	ITEM 10 REL 190 (SHIP VIA: GROUND)(PL121845) 
045702	20	21	ITEM 10 REL 200 (SHIP VIA:FEDEX P1)(PL121624)                                
045702	23	24	ITEM 10 REL 204 (SHIP VIA: UPS OVERNIGHT)(PL121774)

I thought I had an idea, but apparently I did not...

Thanks,
Willie

 

[fredericofonseca]

google for pivot and/or "for xmlpath"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

I tried for xmlpath as I have used it elsewhere, but it didn't do as I had hoped. I thought about pivot, but had hoped there was a better way as I cannot upgrade my databases to compatibility level 100 yet...

 

[fredericofonseca]

put here your attempt with xmlpath and state why it didn't do as you wished. based on the sample data and desired output it should work fine

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

DECLARE @Temp TABLE (SalesOrder char(6), SalesOrderLine tinyint,Expr1 tinyint, Comment varchar(200))
INSERT INTO @Temp(SalesOrder, SalesOrderLine,Expr1, Comment)
VALUES 
('045702', 1, 2, 'ITEM 10 (SHIP VIA: GROUND)(PL119853)'),
('045702', 3, 4, 'ITEM 10 (SHIP VIA: GROUND)(PL119855)') ,      
('045702', 5, 6, 'ITEM 10 (SHIP VIA: GROUND) (PL120208)'),
('045702', 7, 8, 'ITEM 10 (SHIP VIA: GROUND) (PL120210)'),
('045702', 9, 10, 'ITEM 10 (SHIP VIA: GROUND)(PL120209)'),
('045702', 11, 12, 'ITEM 10 REL 180 (SHIP VIA: GROUND)(PL120967)'),
('045702', 13, 14, 'ITEM 10 REL 161 (SHIP VIA: GROUND)(PL121224)'),
('045702', 15, 16, 'ITEM 10 REL 160 (SHIP VIA: GROUND)(PL121491)'),
('045702', 18, 19, 'ITEM 10 REL 190 (SHIP VIA: GROUND)(PL121845)'),
('045702', 20, 21, 'ITEM 10 REL 200 (SHIP VIA:FEDEX'),
('045702', 20, 22, 'P1)(PL121624)'),
('045702', 23, 24, 'ITEM 10 REL 204 (SHIP VIA: UPS'),
('045702', 23, 25, 'OVERNIGHT)(PL121774)')


;WITH MyCte
AS
(

      SELECT SalesOrder,
             SalesOrderLine,
             Expr1,
             CAST(Comment as varchar(max)) AS Comment,
             RC
      FROM (SELECT *,
                   ROW_NUMBER() OVER (PARTITION BY SalesOrder,SalesOrderLine 
                                      ORDER BY SalesOrder,SalesOrderLine, Expr1) AS RC
            FROM @Temp) Tmp
      WHERE RC = 1
     UNION ALL
     SELECT Tmp.SalesOrder,
            Tmp.SalesOrderLine,
            Tmp.Expr1,
            CAST(MyCte.Comment + Tmp.Comment as varchar(max)) AS Comment,
            Tmp.RC
     FROM (SELECT *,
                  ROW_NUMBER() OVER (PARTITION BY SalesOrder,SalesOrderLine 
                                      ORDER BY SalesOrder,SalesOrderLine, Expr1) AS RC
            FROM @Temp) Tmp
     INNER JOIN MyCte ON Tmp.RC = MyCte.RC+1
                     AND Tmp.SalesOrder = MyCte.SalesOrder
                     AND Tmp.SalesOrderLine = MyCte.SalesOrderLine
)

SELECT MyCte.* 
FROM MyCte 
INNER JOIN (SELECT SalesOrder,SalesOrderLine, MAX(RC) AS RC
                   FROM MyCte 
            GROUP BY SalesOrder,SalesOrderLine) Tmp
             
      ON Tmp.RC = MyCte.RC
     AND Tmp.SalesOrder = MyCte.SalesOrder
     AND Tmp.SalesOrderLine = MyCte.SalesOrderLine

Borislav Borissov
VFP9 SP2, SQL Server