CODEWITHSUNDEEP
sqlsql-serversql-server-2005
JustAnotherDeveloper
JustAnotherDeveloper

Reputation: 3199

Creating a stored procedure to export selected columns in a row as a delimited string

I have a piece of code which allows me to take a single string and parse it into columns. However I have no idea where to start with the export. The import is easy enough with a split and insert.

With the export I will need to select about 6 of the 12 columns in table A, combine all these files into a delimited list and then return the string.

I assume that the best way of doing this would be the following?:

I'm fairly comfortable using SQL to select, update, combine and other menial tasks... but I literally have no idea when it comes to stored procs as I always try to avoid them and add functionality into my code instead!

Upvotes: 1

Views: 321

Answers (2)

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

Try this... 

SELECT
        ISNULL(Col6 ,'')
+ '|' + ISNULL(Col7 ,'')
+ '|' + ISNULL(Col8 ,'') 
+ '|' + ISNULL(Col9 ,'') 
+ '|' + ISNULL(Col10,'') 
+ '|' + ISNULL(Col11,'') 
+ '|' + ISNULL(Col12,'')  AS MyList
FROM MyTable
You can cast or convert different datatypes using SQL. (Cast & Convert)
Here's a few examples:

SELECT
        ISNULL(CAST(Col6 as varchar) ,'')
+ '|' + ISNULL(CAST(Col7 as varchar(10)) ,'')
+ '|' + ISNULL(CONVERT(varchar(10),Col8) ,'') 
+ '|' + ISNULL(CONVERT(varchar(7),Col9 ),'') 
+ '|' + ISNULL(Col10,'') 
+ '|' + ISNULL(Col11,'') 
+ '|' + ISNULL(Col12,'')  AS MyList
FROM MyTable

Upvotes: 0

user1049838
user1049838

Reputation:

Oracle Code:

   declare

   function f
   return varchar2
   is
      l_text varchar2(1000);
   begin
      for c in (
      select rownum from <table>
      )
      loop
         l_text := l_text || c.rownum || ';';
      end loop;

      return l_text;

   end;

begin
   dbms_output.put_line(f); 
end;
/

Upvotes: 1

Related Questions