Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

T SQL group values into XML data field

I have a result set:
SECTION      Start_point      End_Point      DEFECT_CODE
SecA      0.00      50.00      RUT
SecA      0.00      50.00      FAT
SecB      0.00      50.00      RUT
SecB      0.00      50.00      CRACK
SecC      0.00      50.00      FRET
SecC      0.00      50.00      RUT
SecC      0.00      50.00      FAT
SecA      50.00      100.00      RUT
SecA      50.00      100.00      FAT
SecA      50.00      100.00      POTHOLE
SecB      50.00      100.00      RUT
SecB      50.00      100.00      CRACK
SecB      50.00      100.00      FRET
SecC      50.00      100.00      FRET
SecC      50.00      100.00      RUT
SecC      50.00      100.00      FAT
SecA      100.00      150.00      RUT
SecA      100.00      150.00      FAT
SecB      100.00      150.00      FRET
SecC      100.00      150.00      FRET
SecC      100.00      150.00      RUT
SecC      100.00      150.00      FAT
SecC      100.00      150.00      FAT
SecC      150.00      200.00      FRET
SecC      150.00      200.00      RUT
SecC      150.00      200.00      POTHOLE
SecC      150.00      200.00      FAT
SecC      200.00      250.00      RUT
SecC      200.00      250.00      POTHOLE
SecC      200.00      250.00      FAT


I need to group this into a table
DECLARE @OutputChoppedDefectXml TABLE
(
       SECTION                  nvarchar(30)      NOT NULL
      ,START_POINT            decimal(9,2)      NOT NULL
      ,END_POINT                  decimal(9,2)      NOT NULL
      ,DEFECT_CODES_XML      xml                        NOT NULL
);

so that output looks roughly like


INSERT INTO @ExampleChoppedDefectXml (SECTION, START_POINT, END_POINT, DEFECT_CODES_XML)
VALUES
       ('SecA', 0, 10, '<DefectCodes>RUT</DefectCodes>')
      ,('SecA', 10, 20, '<DefectCodes>RUT,POTHOLE</DefectCodes>')
      ,('SecA', 20, 50, '<DefectCodes>RUT</DefectCodes>')
      ,('SecA', 50, 60, '<DefectCodes>RUT,FAT</DefectCodes>')
      ,('SecA', 60, 70, '<DefectCodes>RUT,POTHOLE,FAT</DefectCodes>')
      ,('SecA', 70, 100, '<DefectCodes>RUT,FAT</DefectCodes>')
      ,('SecA', 100, 140, '<DefectCodes>FAT</DefectCodes>');


The Defect codes must be ordered according to a table @DefectCode table which has a rank assigned to each code.

Please help with this.
0
DanP1980
Asked:
DanP1980
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is possible, even using different techniques.
I usually use the one with FOR XML PATH('') in a subquery, see here for the different techniques exposed:
http://anexinetbi.blogspot.be/2013/12/groupconcat-functionality-for-sql-server.html
0
 
DanP1980Author Commented:
Thanks Guy sadly my proxy server will block this:

I have written the following which works however it does not add the XML node <DefectCodes> around the values.  I end up with

Select Section, Start_point, End_Point,  
      SUBSTRING (
      (Select ( ',' + t2.DEFECT_CODE)
      
      From #Stage1 t2
      Inner join  @DefectCode dc on dc.DEFECT_CODE = t2.DEFECT_CODE
      Where t1.SECTION = t2.Section and t1.Start_Point = t2.Start_Point and t1.End_Point = t2.End_point
      
      Order by CODE_ORDER, SECTION, Start_Point, End_Point
      for XML Path( '' )
                                    ),2,1000)
                                    
                                    from #Stage1 t1
                                    
      Group by Section, Start_point, End_Point


Section      Start_point      End_Point      (No column name)
SecA      0.00      50.00      RUT,FAT
SecA      50.00      100.00      RUT,POTHOLE,FAT
SecA      100.00      150.00      RUT,FAT
SecB      0.00      50.00      CRACK,RUT
SecB      50.00      100.00      FRET,CRACK,RUT
SecB      100.00      150.00      FRET
SecC      0.00      50.00      FRET,RUT,FAT
SecC      50.00      100.00      FRET,RUT,FAT
SecC      100.00      150.00      FRET,RUT,FAT,FAT
SecC      150.00      200.00      FRET,RUT,POTHOLE,FAT
SecC      200.00      250.00      RUT,POTHOLE,FAT
0
 
DanP1980Author Commented:
I have found a solution in the end.
0
 
SharathData EngineerCommented:
try like this.
SELECT [Section], 
       Start_point, 
       End_Point, 
       '<DefectCodes>' + DEFECT_CODES_XML 
       + '</DefectCodes>' AS DEFECT_CODES_XML 
  FROM (SELECT [Section], 
               Start_point, 
               End_Point, 
               SUBSTRING ((SELECT ( ',' + t2.DEFECT_CODE ) 
                             FROM #Stage1 t2 
                                  INNER JOIN @DefectCode dc 
                                          ON dc.DEFECT_CODE = t2.DEFECT_CODE 
                            WHERE t1.SECTION = t2.Section 
                              AND t1.Start_Point = t2.Start_Point 
                              AND t1.End_Point = t2.End_point 
                            ORDER BY CODE_ORDER, 
                                     SECTION, 
                                     Start_Point, 
                                     End_Point 
                           FOR XML Path( '' )), 2, 1000) AS DEFECT_CODES_XML 
          FROM #Stage1 t1 
         GROUP BY Section, 
                  Start_point, 
                  End_Point) T1 

Open in new window

0
 
DanP1980Author Commented:
This is what I had come up with so it is good to get confirmation!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now