Fetch Data from Multiple Records

You can use function to fetch data from multiple records into one field. If for example we have table users and hobbies.

1 John Walker Bangladesh
2 Frank McLane Nepal
3 Leon Kennedy USA

1 Sleeping
1 Walking
2 Swimming
3 Help People

If we're asked to display the user's name along with his/her hobbies, first we create a function to fetch the hobbies of a user into a single column. The function is as follows

CREATE FUNCTION [dbo].[get_hobbies]

@usr_id int
RETURNS varchar(255)
DECLARE @hobbies varchar(255)
DECLARE @temp varchar(255)

SET @hobbies = ''
SET @temp = ''

DECLARE HbCur CURSOR FOR SELECT hobby FROM users inner join hobbies on users.[user_id] = hobbies.[user_id] WHERE users.[user_id]=@usr_id

FETCH next FROM HbCur INTO @temp
WHILE @@Fetch_Status = 0
SET @hobbies = @hobbies + @temp + ', '
FETCH next FROM HbCur INTO @temp


--remove the last unnecessary comma
SET @hobbies = substring(@hobbies, 1, len(@hobbies)-1)

RETURN @hobbies

The function is quite self-explanatory. The body of the function starts after the keyword BEGIN and end at the END keyword. It takes the user_id as a parameter which will be used to select all hobbies of that user. Here, two variables are declared, @hobbies and @temp. These two variables are used to store the result of the hobbies queried. Next, we declare a CURSOR that will hold the result hobbies from the query, and then can be iterated to get each hobby. Next, there is a loop which will fetch every single hobby to the @temp variable which will then be appended to @hobbies variable that will hold all of the hobbies separated by comma. Last, the function returns all of the hobbies.

To test the function you can run the following sql

SELECT [dbo].[get_hobbies] (1)

This is the output when I run the query.


ORACLE: Load Data from a File

If you're asked to load data from a file in Oracle, you can use the external tables. External tables allow you to query data from a file and treat it almost like a table (there are some limitations to external tables such as you can't create index, you can't INSERT or UPDATE an external tables). Let's practice through an example.

Let's say that we have this csv file named records.csv


The first step is to determine which folder you want this file to be put into. What you should concern here is that Oracle should have the read and write access right to the file. For this example, we create a new directory in 'C:\Data'. After physically creating the directory, we then create a directory in Oracle

CREATE DIRECTORY records_dir AS 'C:\Data';

Here, records_dir is the unique name of the directory, you can name you directory to something else.

Then we create the table which defines the external table. In the CREATE TABLE statement, we define what fields are in the external table, where is the directory for the external table, the filename, etc.. For this example we create a table named records_table

CREATE TABLE records_table (

record_no int,

code int,

message varchar2(4),

amount number(8,2),

message2 varchar(6)





records delimited by newline

fields terminated by ','


LOCATION ('records.csv')


Because the file is a csv file, we define the terminating character for each field as ','. External table can also load data from a fixed column file.

We can then select the data from the external table by using the SELECT statement (you can also add the WHERE clause)

SELECT * FROM records_table

Here is the output example run from toad

You can then insert the data from an external table to your table.

CREATE TABLE in_records_table as

(SELECT * FROM records_table);

You can also set the REJECT LIMIT parameter of the external table. If Oracle found any error while processing a record, the record will be rejected. And if the number of rejected records exceeded the REJECT LIMIT parameter, your select statement will fail. You can change the REJECT LIMIT parameter by the statement


After processing the select statement of an external table, Oracle creates some files which are the bad file, discard file and the log file. The bad file contains all of the rejected records, while in the log file you can see Oracle activity when processing the external table like why a record was rejected. By default, these files are created in the same directory with the input file.

Another way to load data from a file to Oracle is by using the sqlldr command. You can see here for how to use the command.


Findstr Command

Sometimes we need to count how many lines are there in a file. This could be easy if we only have to count all of the line/records in the file. But, we might need to count only records containing a certain string. We can do this with the DOS findstr and find command.

The syntax of the findstr command is

findstr "certain string" file.txt

Above is the simplest syntax to use findstr. You can see the full options list by typing /?. You can use /N to display also the line number in front of each line. You can use the /C command to search for contiguous string that contains space character. At the example above, the findstr will find every line that contain "search" or "string" in file.txt. If you use /C:"certain string", the findstr will find "certain string" in the file. You can also pipe the output to another command or redirect the output to another file.

I was once asked to count the number of records in a file of which transaction record is 200, the amount is zero, etc. Because the file is large, doing it manually will be tedious. Here's the findstr command form that I use.

findstr " 200 " file.txt | findstr /N " 0.00 " > out.txt

At the command above, I first search for the lines containing the string " 200 ", then I redirect the output to another findstr command that will search lines containing " 0.00 ". So, all the commands do is searching for lines that contain both " 200 " and " 0.00 ". In the end, the output is redirected to the out.txt file. Which after that, I can open out.txt to check for the result. With the /N option, the lines in out.txt will have line number.

I redirect the output to a file so that I can check and process further the output for another purpose. If you're sure all of the output lines are the line you want, you can pipe the output from findstr command to the find command by using the option /C.

findstr " 200 " file.txt | findstr " 0.00 " | find /C "200"


SQL: Selecting Columns from Different Rows

There might be times when you have to show values from different records as a row. This could for reporting need and based on the design of the table in the database. Let's say that you have a table that looks like the following:

NameTax Type Number1Number2
Roy Raphael HousingNULL5000
Sam Murphy Medical1000NULL
Sam Muprhy HousingNULL3000
Roy Raphael Medical800NULL

If you want to display the sum all of the tax types for a single person in a row, one way to do this is by using the case keyword. This article assume that there's only two tax types in the table. So, no query for dynamic number of columns is covered. The expected result could be like the following:

Roy Raphael 5000800
Sam Murphy 30001000

By using the case keyword, we can filter which data will be put to which column.

select sum(case when col1 = 'value' then col2 end) as ps-column

In the case above, if the Tax Type value is 'Housing' we put it in pseudo-column named Housing and so we do the same for the Medical tax. The end result of the query might be:

select [name], sum(case when [Tax Type] = 'Housing' then isnull(Cost, 0) end) as Housing, sum(case when [Tax Type] = 'Medical' then isnull(Cost, 0) end) as Medical from taxes group by [name]

The above sql language is a specific sql for SQL server. The isnull function is used so that the sum will just treat null values as 0.