Split a table data accourding to field number.

Question

1.00/5 (1 vote)

See more:

respected sir,
i have a table data in sql server database like below where fields like teacher and class1,class2,.......

Teacher cls1 cls2 cls3 cls4 cls5 cls6 cls7 cls8 cls9 cls10
Ram r1 r2 r3 r4 r5 r6 r7 r8 r9 r10
Sam s1 s2 s3 s4 s5 s6 s7 s8 s9 s10
Hari h1 h2 h3 h4 h5 h6 h7 h8 h9 h10
Gagan g1 g2 g3 g4 g5 g6 g7 g8 g9 g10
Prakas p1 p2 p3 p4 p5 p6 p7 p8 p9 p10

I want to split the table like teacher with five classeslike below in c#
Teacher cls1 cls2 cls3 cls4 cls5
Ram r1 r2 r3 r4 r5
Sam s1 s2 s3 s4 s5
Hari h1 h2 h3 h4 h5
Gagan g1 g2 g3 g4 g5
Prakas p1 p2 p3 p4 p5

Teacher cls6 cls7 cls8 cls9 cls10
Ram r6 r7 r8 r9 r10
Sam s6 s7 s8 s9 s10
Hari h6 h7 h8 h9 h10
Gagan g6 g7 g8 g9 g10
Prakas p6 p7 p8 p9 p10

kindly help me.
thanks in adv.

Posted 24-Jun-13 20:23pm

connect2manas

Add a Solution

Comments

Shafeequl 25-Jun-13 2:34am

i thinj you can obtain this through sql query ??

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-06-24T20:38:00

Solution 1

Both approaches are nonsense - neither is good for anything.
This is a typical 1:N (one to many) situation:

Teacher(id, name)<br />
Class(id, teacher_id, name)

Still, let's suppose you stick to this horrible approach. Let T be the original table and T1, T2 the two resultant ones.
Simply issue these:

SQL

INSERT INTO T1(teacher, cls1, cls2, cls3, cls4, cls5) SELECT teacher, cls1, cls2, cls3, cls4, cls5 FROM T;
INSERT INTO T2(teacher, cls6, cls7, cls8, cls9, cls10) SELECT teacher, cls6, cls7, cls8, cls9, cls10 FROM T;

Of course, you will need to create the T1 and T2 tables.

Posted 24-Jun-13 20:38pm

Zoltán Zörgő

Updated 24-Jun-13 20:40pm

v2

Comments

connect2manas 25-Jun-13 2:47am

Dear Zoltan ,
Thanks for your respons.
but i am getting this type of value from sql analysis service.
so my requirement is to print the teacher name with number of column to display because we can't display all the number of columns(like 100 columns).so i want the above requirement.
kindly help me.

Zoltán Zörgő 25-Jun-13 2:53am

Than follow the second approach I have outlined.

connect2manas 25-Jun-13 3:00am

number of tables increases dynamically,so how will i conform that number of tables have to create(T1,T2).
dynamically we have to create the tables.

Zoltán Zörgő 25-Jun-13 3:20am

Ok. You have missed this really important information from your original post.

Zoltán Zörgő 25-Jun-13 3:23am

Based on what is fixed, you could use PIVOT or UNPIVOT to rotate result table and simply select from it what you need.