1

I have the following two tables in sql.I want to get the calendarId from calenderschedule and join with calendar table to get the calendarcode for each productId. Output format is described below. MS SQL Server 2012 version string_split is not working. Please help to get the desired output.

Table1: calenderschedule

productid, calendarid
100        1,2,3
200        1,2

Table2: calendar

calendarid, calendarCode
1           SIB 
2           SIN 
3           SIS

Output:

productId, calendarCode
100        SIB,SIN,SIS
200        SIB,SIN
Kanimozhi Mayilsamy
  • 7
  • 2

1 Answers1

0

You can normalize the data by converting to XML and then using CROSS APPLY to split it. Once it's normalized, use the STUFF function to combine the calendar codes into a comma-separated list. Try this:

;WITH normalized_data as (
SELECT to_xml.productid
 ,split.split_calendarid 
FROM
     (
     SELECT *,
        cast('<X>'+replace(cs.calendarid,',','</X><X>')+'</X>' as XML) as xmlfilter
     FROM calendarschedule cs
     ) to_xml
 CROSS APPLY
     ( 
     SELECT new.D.value('.','varchar(50)') as split_calendarid 
     FROM to_xml.xmlfilter.nodes('X') as new(D)
     ) split
)   select distinct
        n.productid
        ,STUFF(
             (SELECT distinct ', ' + c.calendarCode
              FROM calendar c
              JOIN normalized_data n2 on n2.split_calendarid = c.calendarid
              WHERE n2.productid = n.productid
              FOR XML PATH ('')), 1, 1, '') calendarCode
    from normalized_data n

I feel like this solution is a bit overly complex, but it's the only way I got it to work. If anybody knows how to simplify it, I'd love to hear some feedback.

cpalmer
  • 311
  • 2
  • 9