Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Concatenating all fields in a resultset

Concatenating all fields in a resultset

Scheduled Pinned Locked Moved Database
question
6 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • D Offline
    D Offline
    Dewald
    wrote on last edited by
    #1

    I'm looking for a way to concatenate all the fields in a result set to one value. If I know the names of the fields, it is fairly easy:

    SELECT Field1 + Field2 + Field3 FROM MyTable

    although it is a bit more tricky if one or more of the fields are numeric in which case the following would do:

    SELECT CAST(Field1 AS VARCHAR) + CAST(Field2 AS VARCHAR) + CAST(Field3 AS VARCHAR) FROM MyTable

    The above snippets would return a long string (a concatenation of all the fields) for each row in the table. But what if I don't know the names, number of and types of fields in the table? Is there a way to still accomplish the above? Thanks in advance.

    D M 2 Replies Last reply
    0
    • D Dewald

      I'm looking for a way to concatenate all the fields in a result set to one value. If I know the names of the fields, it is fairly easy:

      SELECT Field1 + Field2 + Field3 FROM MyTable

      although it is a bit more tricky if one or more of the fields are numeric in which case the following would do:

      SELECT CAST(Field1 AS VARCHAR) + CAST(Field2 AS VARCHAR) + CAST(Field3 AS VARCHAR) FROM MyTable

      The above snippets would return a long string (a concatenation of all the fields) for each row in the table. But what if I don't know the names, number of and types of fields in the table? Is there a way to still accomplish the above? Thanks in advance.

      D Offline
      D Offline
      David Mujica
      wrote on last edited by
      #2

      You would have to mess around with sys.columns sys.types to determine the datatype of the columns dynamically. Of course you would have to know the table and column name before trying to get the datatype. OR Are you constrained by using TSQL ? If you write a .NET application and put the results into a Datatable, you could then find the datatype by using something like: Datatable.Columns.GetType() Good luck :thumbsup:

      D 1 Reply Last reply
      0
      • D David Mujica

        You would have to mess around with sys.columns sys.types to determine the datatype of the columns dynamically. Of course you would have to know the table and column name before trying to get the datatype. OR Are you constrained by using TSQL ? If you write a .NET application and put the results into a Datatable, you could then find the datatype by using something like: Datatable.Columns.GetType() Good luck :thumbsup:

        D Offline
        D Offline
        Dewald
        wrote on last edited by
        #3

        Thanks, I was hoping to be able to do it using SQL only but I eventually decided to do it from inside a C# app which does make it a lot simpler.

        1 Reply Last reply
        0
        • D Dewald

          I'm looking for a way to concatenate all the fields in a result set to one value. If I know the names of the fields, it is fairly easy:

          SELECT Field1 + Field2 + Field3 FROM MyTable

          although it is a bit more tricky if one or more of the fields are numeric in which case the following would do:

          SELECT CAST(Field1 AS VARCHAR) + CAST(Field2 AS VARCHAR) + CAST(Field3 AS VARCHAR) FROM MyTable

          The above snippets would return a long string (a concatenation of all the fields) for each row in the table. But what if I don't know the names, number of and types of fields in the table? Is there a way to still accomplish the above? Thanks in advance.

          M Offline
          M Offline
          Mycroft Holmes
          wrote on last edited by
          #4

          You can use syscolumns or the system views and basically build a string in TSQL using a loop or cursor and the execute the string. This is a real kludge but there is no other way that I know of. Seems like a rather silly requirement!

          Never underestimate the power of human stupidity RAH

          D 1 Reply Last reply
          0
          • M Mycroft Holmes

            You can use syscolumns or the system views and basically build a string in TSQL using a loop or cursor and the execute the string. This is a real kludge but there is no other way that I know of. Seems like a rather silly requirement!

            Never underestimate the power of human stupidity RAH

            D Offline
            D Offline
            Dewald
            wrote on last edited by
            #5

            Yeah it probably is a silly requirement. The reason I wanted to do so is as follows. I have a number of tables that I need to check for changes on a regular basis. I can't add columns to the tables so I have to compare them against "backup copies". I decided to create a mirror table for each that holds the last saved version of the table. When changes are made to the original table I wanted my script to compare the table against mine and then process only those rows that are different. To compare an entire row with my backup table's row, I thought I'd return the row as I described above (ie a long concatenated string). This way I wouldn't need to compare it field by field. In the end I decided to do most of the work in C# anyway so it didn't matter that I'd have to compare the rows field by field.

            M 1 Reply Last reply
            0
            • D Dewald

              Yeah it probably is a silly requirement. The reason I wanted to do so is as follows. I have a number of tables that I need to check for changes on a regular basis. I can't add columns to the tables so I have to compare them against "backup copies". I decided to create a mirror table for each that holds the last saved version of the table. When changes are made to the original table I wanted my script to compare the table against mine and then process only those rows that are different. To compare an entire row with my backup table's row, I thought I'd return the row as I described above (ie a long concatenated string). This way I wouldn't need to compare it field by field. In the end I decided to do most of the work in C# anyway so it didn't matter that I'd have to compare the rows field by field.

              M Offline
              M Offline
              Mycroft Holmes
              wrote on last edited by
              #6

              I would use a different approach, I presume you can add a new table to the database even if you can't change an existing table. So have a log table to record changes, depending on requirements it could be as little as and Id, modified and modifiedby columns for each table you are monitoring. Changes can be logged by spit triggers, this is one of the very few valid uses for triggers. If you need a from => to audit then a more detailed logging process can be implemented.

              Never underestimate the power of human stupidity RAH

              1 Reply Last reply
              0
              Reply
              • Reply as topic
              Log in to reply
              • Oldest to Newest
              • Newest to Oldest
              • Most Votes


              • Login

              • Don't have an account? Register

              • Login or register to search.
              • First post
                Last post
              0
              • Categories
              • Recent
              • Tags
              • Popular
              • World
              • Users
              • Groups