Avatar of tommym121
tommym121
Flag for Canada 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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
tommym121

8/22/2022 - Mon
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
tommym121

ASKER
Thanks