1

I have several data concatenated in one cell delimited by "," separator. Below is the screen shot for data and output i required. I know how to convert from output to concatenate format by using For XML but i am unable to convert concatenate to the output format.

enter image description here

I am using Sql server 2008. Kindly help to accomplish this.

Regards, Ratan

RRO
  • 71
  • 1
  • 7
  • What have you tried so far? Google SQL Split() function for some ideas as well. – Sparky Apr 05 '15 at 11:43
  • possible duplicate of http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Khurram Ali Apr 05 '15 at 12:07
  • @RRO please don't hesitate to give a feedback, you asked for Kindly help and I tried for you – void Apr 05 '15 at 14:19

1 Answers1

0

as you have composite string in both columns then I prefer to use cross joins with convert to xml, twice :

  SELECT MemberId = y.i.value('(./text())[1]', 'nvarchar(1000)'),
         TokenId  = u.j.value('(./text())[1]', 'nvarchar(1000)')             
  FROM 
  ( 
    SELECT 
        m = CONVERT(XML, '<i>' 
            + REPLACE(MemberId, ',' , '</i><i>') 
            + '</i>').query('.'),
        t= CONVERT(XML, '<j>' 
            + REPLACE(TokenId, ',' , '</j><j>') 
            + '</j>').query('.')
    FROM member_tokens
  ) AS a 
  CROSS APPLY m.nodes('i') AS y(i)
  CROSS APPLY t.nodes('j') AS u(j)

SQLFIDDLE DEMO

void
  • 7,760
  • 3
  • 25
  • 43
  • thanks a lot, its working for me. Could you please explain the last two lines cross apply m.node('I') as y(I). Once again thanks a lot. I was trying to do it by using split function but I was not getting the full output because it had composite string in both the columns. – RRO Apr 05 '15 at 21:18
  • you are very welcome, `(query) as a cross apply m.node('i')` is to use concatenated memberId(which is converted to XML) in each row for the node('i') which extracts memberids. and `as y('i')` is an alias to use the extracted values as something like table(field). – void Apr 05 '15 at 21:40
  • Thanks a lot once again, it was very helpful – RRO Apr 08 '15 at 07:29