Urgent development question

Question

Urgent development question

RPSQL

Hall of Fame

Points: 3229
More actions
January 25, 2010 at 9:53 am

#216074

urgent development question:
i have a table which contains following columns:
name,
student id,
subjects
in subjects column we have subjects taken by each student seperated by semicolumn.i.e. "english;social study;crafts" etc.
now, curre, I want output table which is seperated by each subject...i.e.
before
------
name | student id | subjects
stdA| 123| english;social study
stdB| 234| social study;crafts
After
-----
name | student id | subjects
stdA| 123| english
stdA| 123| social study
stdB| 234| social study
stdB| 234| social crafts
....
Please help me as this is urgent.
Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why is this urgent? Homework with an upcoming deadline?

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

We don't do homework questions. We are happy to help you figure things you, but you need to make an attempt to get some initial work done and show some code you've written.

RPSQL Hall of Fame Points: 3229 More actions · Answer 3

I didn't submitted the correct data as I don't want to send company data out...Next time I will make sure to put question in right format...sorry this time...Plz let me know if anyone has any idea...Thanks in advance...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Here is a thread that may help you find your solution. You will still need to look at the code and determine how to adapt it to your situation. Thus no direct code answer, but an answer as to how to find your answer:-D

http://www.sqlservercentral.com/Forums/Topic845680-338-1.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

RPSql (1/25/2010)
I didn't submitted the correct data as I don't want to send company data out...Next time I will make sure to put question in right format...sorry this time...Plz let me know if anyone has any idea...Thanks in advance...

Even the same data that you posted would do but in a consumable format as suggested by Gail 🙂

---------------------------------------------------------------------------------

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 6

Just a thought...

If this is development, you may want to review the design.

Having a table for Subject ( with just a Code and Description for each Subject ) might allow you to more naturally get to the results you are seeking.

Say data was populated from a web form, would you have a drop down with every available combination of subects? Or allow freeform text to be entered? And then how would you validate user input?

Greg E

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Greg Edwards-268690 (1/25/2010)
Just a thought...
If this is development, you may want to review the design.

Absolutely. Comma-delimited lists in a column are a violation of 1st Normal Form and are an absolute pain to deal with. This is the easy part, trying to add or remove 'subjects' from the list is far harder.

Take the time to properly normalise the table, and you won't have problems like this one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

GilaMonster (1/25/2010)
Greg Edwards-268690 (1/25/2010)
Just a thought...
If this is development, you may want to review the design.
Absolutely. Comma-delimited lists in a column are a violation of 1st Normal Form and are an absolute pain to deal with. This is the easy part, trying to add or remove 'subjects' from the list is far harder.
Take the time to properly normalise the table, and you won't have problems like this one.

Taking it a step further - normalize any tables that have any (comma,semi-colon, etc) delimited columns

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 9

GilaMonster (1/25/2010)
Greg Edwards-268690 (1/25/2010)
Just a thought...
If this is development, you may want to review the design.
Absolutely. Comma-delimited lists in a column are a violation of 1st Normal Form and are an absolute pain to deal with. This is the easy part, trying to add or remove 'subjects' from the list is far harder.
Take the time to properly normalise the table, and you won't have problems like this one.

Even worse is if a subject needs to be renamed. What if "English" becomes "Language Arts" (as it did for me while I was in high school)? Can't just simply "replace" it, because you could also have a class on "Advanced English Literature", or "English as a Second Language", and suddenly you'd mess those up. Can become quite tricky.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 10

GSquared (1/25/2010)
GilaMonster (1/25/2010)
Greg Edwards-268690 (1/25/2010)
Just a thought...
If this is development, you may want to review the design.
Absolutely. Comma-delimited lists in a column are a violation of 1st Normal Form and are an absolute pain to deal with. This is the easy part, trying to add or remove 'subjects' from the list is far harder.
Take the time to properly normalise the table, and you won't have problems like this one.
Even worse is if a subject needs to be renamed. What if "English" becomes "Language Arts" (as it did for me while I was in high school)? Can't just simply "replace" it, because you could also have a class on "Advanced English Literature", or "English as a Second Language", and suddenly you'd mess those up. Can become quite tricky.

Exactly why we always have the code on the fact record, and have a reference table with code and description. Descriptions tend to change over time for a number of reasons, and updating millions of fact records vs. 1 reference table is much easier.

A couple of these tips should prove very valuable.

GE

WayneS SSC Guru Points: 95409 More actions · Answer 11

CirquedeSQLeil (1/25/2010)
Here is a thread that may help you find your solution. You will still need to look at the code and determine how to adapt it to your situation. Thus no direct code answer, but an answer as to how to find your answer:-D
http://www.sqlservercentral.com/Forums/Topic845680-338-1.aspx

Ahh yes, I remember this thread. It was fun, and it would definitely guide the OP on a solution for his problem.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

WayneS (1/25/2010)
CirquedeSQLeil (1/25/2010)
Here is a thread that may help you find your solution. You will still need to look at the code and determine how to adapt it to your situation. Thus no direct code answer, but an answer as to how to find your answer:-D
http://www.sqlservercentral.com/Forums/Topic845680-338-1.aspx
Ahh yes, I remember this thread. It was fun, and it would definitely guide the OP on a solution for his problem.

That was an awesome thread. You guys had a lot of fun on that one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jeff Moden (1/25/2010)
WayneS (1/25/2010)
CirquedeSQLeil (1/25/2010)
Here is a thread that may help you find your solution. You will still need to look at the code and determine how to adapt it to your situation. Thus no direct code answer, but an answer as to how to find your answer:-D
http://www.sqlservercentral.com/Forums/Topic845680-338-1.aspx
Ahh yes, I remember this thread. It was fun, and it would definitely guide the OP on a solution for his problem.
That was an awesome thread. You guys had a lot of fun on that one.

Yes yes - good thread it was.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events