1

I have a table with 20 columns, where column 2 is an ntext, and it contains 133 comma separated 'fields'. How do I read the 19 columns and include in the output, the 133 columns from the ntext column.

I am using SQL 2008 and my query is a simple SELECT all columns from tablename using SQL Server Management Studio. What I am trying to achieve is that when columns 1 and 3 thru 19 are returned, I would also like returned the 133 'columns' that are stored within the second column [CSV]. SQL below:

SELECT TOP 1000 [ACL]
      ,[CSV]
      ,[EMPLOYER_ABN]
      ,[EMPLOYER_ID]
      ,[EMPLOYER_NAME]
      ,[FD_SS_CONTRIBUTIONS_REP_KEY]
      ,[MEMBER_ADDRESS_LINE_1]
      ,[MEMBER_ADDRESS_LINE_2]
      ,[MEMBER_ADDRESS_LINE_3]
      ,[MEMBER_ADDRESS_LINE_4]
      ,[MEMBER_ADDRESS_USAGE]
      ,[MEMBER_CLIENT_IDENTIFIER]
      ,[MEMBER_COUNTRY]
      ,[MEMBER_DOB]
      ,[MEMBER_EMAIL_ADDRESS]
      ,[MEMBER_EMPLOYMENT_END_DATE]
      ,[MEMBER_EMPLOYMENT_END_REASON]
      ,[MEMBER_FAMILY_NAME]
      ,[MEMBER_GENDER]
      ,[MEMBER_GIVEN_NAME]
      ,[MEMBER_ID]   FROM [Sonora].[dbo].[FD_SUPERSTREAM_CONTRIBUTIONS]
johnwalker10
  • 45
  • 8
  • i don't understand your question, could you give a sample of your expected output? – A ツ Sep 03 '15 at 08:09
  • @Aツ He want's sth like [this](http://stackoverflow.com/questions/11185318/how-to-separate-string-into-different-columns) – Lukasz Szozda Sep 03 '15 at 08:11
  • @johnwalker, basically you need function to split your string in chunks and probably dynamic PIVOT otherwise you need to do : `SELECT ID, my_split_func(text, 1), my_split_func(text, 2), ... , my_split_func(text, 130) FROM table...` – Lukasz Szozda Sep 03 '15 at 08:16
  • Why is your current query not OK? It seems to me that whatever the query returns is a valid csv (if you concatenate the other columns with commas, that is). As a side-note, i would recommend leaving the text column last in the query, that way if new fields appear in it, it won't impact backwards-compatibility that much. – Mihai Ovidiu Drăgoi Sep 03 '15 at 08:24

0 Answers0