asked on

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

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

NikolasG

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

TempDBA

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.

tommym121

ASKER

TempDBA,

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.

tommym121

ASKER

NikolasG,

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?

ASKER CERTIFIED SOLUTION

NikolasG

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tommym121

ASKER

Thanks