SQL Select columns with columnized ntext data

Tags:
SQL
SQL Server
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.


Software/Hardware used:
SQL server
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Post us your SQL and we can take a look.
    133,790 pointsBadges:
    report
  • PeterGrierson

    Hi ToddN
    We are 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]

    45 pointsBadges:
    report
  • TheRealRaven
    Are you saying that CSV is empty when you run that SELECT? Or are you wanting to return 133 additional items in the SELECT list?
    35,660 pointsBadges:
    report
  • PeterGrierson

    The csv column is full of data, in all instances representing 133 fileds that are separated by columns. Here is a 1 row example of the data within that column and as you can see it contains 132 commas, with some data interleaving.

    Effectively the result will resemble the 'Text to Columns' function within Excel

    ID72262:Contribution.99999994827.18611,99999994827,urn:oasis:tc:ebcore:partyidType:ABN:0151,QUICKSUPER,False,99999994827,QuickSuper,QuickSuper Customer Care,NOT_APPLICABLE,,quicksuper@westpac.com.au,9999999861,99999994827,QuickSuper,032938,999999,QuickSuper,9999999999,SSP0001AU,Statewide,,DirectCredit,2015-02-09,QUICKSPR99999999,,439.95,065000,99999999,Statewide EFT,99999999,,Operations Pty Limited,,142065998,MR,,SMITH,BOB,JEFF,1,1964-02-06,RES,99 ZZZZZZ DRIVE,,,,ELDERSLIE,2570,NSW,au,zzz@hotmail.com,0246580262,0412576998,3117452,3400104,,,2015-01-04,2015-01-17,439.95,0.0,0.0,0.0,0.0,0.0,0.0,0,,,,0.00,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

    45 pointsBadges:
    report
  • ToddN2000
    IS it possible you are exceeding the max for the field?
    ntext

    Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym forntext is national text.

    133,790 pointsBadges:
    report
  • carlosdl
    Not a good idea to store things that way...  #JustSaying
    84,805 pointsBadges:
    report
  • carlosdl
    Not sure if I missed something, but you said what you would like to happen, but have not told what is actually happening.  What do you see in place of the data from that field?
    84,805 pointsBadges:
    report
  • TheRealRaven
    First thing I'd do is get rid of any column storing 'comma-separated values' and make them actual columns. Any time I wanted them retrieved or reported or used as CSVs, I'd access them via a UDF or other method.

    That'd let me use them as CSVs whenever I wanted while also letting SQL do what SQL is supposed to do, rather than always trying to make SQL work in ways that don't fit its design.

    That doesn't help your current problem unfortunately, but a UDF that separates the values and maybe a VIEW that returns the values as columns could help. Joining to the VIEW could give the values as columns.
    35,660 pointsBadges:
    report
  • PeterGrierson

    Thank you to all so much for your replies.

    To clarify my dilema, I am reading a datatable from a third party supplier. The CSV column is of their making, I just need to read the data via SQL Query that retuns both the table columns as they appear, plus the values stored in column 'CSV' represented as columns. The longest record in this [CSV] column so far is only 1200 characters

    From what I have described previously, I am hoping that you can supply a piece of code that will return [ACL], [CSV], [EMPLOYER_ABN] etc down to [MEMBER_ID], plus the contents of [CSV] separated into columns as [CSV-1], [CSV-2],  . . .  [CSV-133]

    Does this make sense!

    Thank you all for your consideration

    45 pointsBadges:
    report
  • TheRealRaven
    What do you want to do with the selected CSV columns? In particular, are you wanting to put the data into a permanent relational format? Or do you need to keep it as "CSV" and run queries over it in different future queries? Or do you want to extract various parts to update into other tables? Do you need to extract data 'one time'? Or will it always be extracted in the future? Do you always need to extract all 133 "columns" or will it be different ones at different times? Was it supplied as a .CSV file and you inserted the CSVs into your table without extracting the values first? Or did the 3rd-party send you the table with the CSVs already embedded in it? (If so, did you pay them for it?)
    35,660 pointsBadges:
    report
  • PeterGrierson
    I simply want to return some of the 'columns' from the CSV-column, to display on a report to aid in data management. The values stored in this CSV-column are used by the supplier to populate an online screen, but this is proprietry usage so we are not entitlled to the code. We will even have to establish our own mappping structure. Hope this helps.
    45 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: