SQL - how to take a record and split into multiple reords

tommym121 used Ask the Experts™
I have the following data , ID and IID is primary key
ID                   IID           Hobbies              Name             Result      
---------+-------------+-------------------+--------------+----------------------- +
2345      HJ63G           Football        Jan                Hobbies,Name
1234      OA71F           Soccer          Mary               Hobbies      
4567      AL23R           Reading         Alexi              Name
5678      RH21B           NULL              Heather   Name

Based on the column 'Result', would like to split into multiple rows.  For example, Row 1 has hobbies,Name in Result,  I will like to generate two records (one for Hobbies and one for Name) like the following.  How can I structure the Select statement(s) to do that

ID                   IID           Hobbies              Name             Result                      VAL
---------+-------------+-------------------+--------------+----------------------- +-----------------
2345      HJ63G           Football        Jan                Hobbies,Name       Hobbies
2345      HJ63G           Football        Jan                Hobbies,Name       Name

And for the entire table it should look like this.

ID                   IID           Hobbies              Name             Result                      VAL
---------+-------------+-------------------+--------------+----------------------- +-----------------
2345      HJ63G           Football        Jan                Hobbies,Name       Hobbies
2345      HJ63G           Football        Jan                Hobbies,Name       Name
1234      OA71F           Soccer          Mary               Hobbies                    Hobbies  
4567      AL23R           Reading         Alexi              Name                        Name
5678      RH21B           NULL              Heather   Name                        Name
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Hello if there is always up to two conditions on the Result row the following should do what you want
Select ID,IID,Hobbies,Name,Result, Val from 
(select ID,IID,Hobbies,Name,Result,SUBSTRING ( Result ,1,CHARINDEX (',' ,Result)  ) as VAL from Table
union all 
select ID,IID,Hobbies,Name,Result,SUBSTRING ( Result ,CHARINDEX (',' ,Result),LEN(Result)) as VAL from Table) tables
where Val is not null

Open in new window

Hope it helps

There are following things you need to reconsider first:-
1. IID is a primary key, so you need to drop the key from the column or change the primary key value for the record.
2. Is it a good design. In the result you are showing both Name and Hoobies but in the Val, each one is coming one by one.
3. What impact it will make in the foreign tables if any.
4. Do, you have index on IID. If yes, how much time will it take to drop and recreate the index on the new column.



1. The table is made from a query. Yes, I can create a identity column,
2. The Result column comes from the fact that I compare corresponding column of tables with similar structure. I will append the name of the column to the Result Column if the columns have different in value.
3. There is no foreign key.  The table is temporary, it only facilitate to identify the changes in two different tables (T1 and T2)
4. No I have no index on IID, performance is not a particular issue.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!



Thanks for your reply,  I am looking at your solution.  My Results is not fixed, It can have more than 2 substrings.

How would you suggest to handle that?
Hello again.
Unfortunately this can be handled with more unions to the 3rd, 4rth value of your Results.
Since MS SQL doesn't have any built in functions (that I know of) to find the 2nd or 3rd occurrence of a string we ll need to make a function to do that
The function would go something like this
create or replace function zfindnextpos(mainstring varchar, searchstring varchar, occurance integer) return  int is
  i int;
  curpos int;
  Result int;
while (i< occurance and curpos< length(mainstring) and curpos!=0) loop
end loop;
if curpos=0 then result:=-1; else result:=curpos; end if;
return result;

Open in new window



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial