split the concated value

• RockingAdmin

  SSCrazy

  Points: 2828

  More actions

  February 10, 2011 at 5:39 am

  #234728

  HI

  i have used the below query to concat values based on group of values. it works fine. But i want to desplit the concated values of the below query result

  select DISTINCT tmplid,REPLACE(RTRIM((SELECT CAST(code AS VARCHAR(MAX)) + ' ' FROM ActionTempl WHERE (tmplid = Results.tmplid ) FOR XML PATH (''))),' ','-') AS tmplcode

  FROM ActionTempl Results

  WHERE not exists (select tmplid from ActionTempl as b where ((b.(tmplid =Results.(tmplid )and code not IN ('1','2','3','4','5,','6','7','8')))

  GROUP BY tmplid

  the output of the above query wil be

  tmplid code

  1 1

  2 1

  3 1-2

  4 1-2-3

  5 2-1

  Required output:

  tmplid code

  1 1

  2 1

  3 1

  3 2

  4 1

  4 2

  4 3

  5 2

  5 1

  Please suggest me with out much changes in the query

  Thanks,

  Rock

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 10, 2011 at 12:50 pm

  #1285428

  If I'm not overlooking something, you could simply replace the REPLACE(FOR XML) subquery with the code column itself.

  What makes me wondering: how did you come up with the concatenated version of the query and having trouble with the base version at the same time? :ermm:

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]