troubleshooting Question

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

Avatar of tommym121
tommym121Flag for Canada asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
6 Comments1 Solution398 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
NikolasG

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros