how get this query for this table

Question

1.00/5 (3 votes)

See more:

how have this table

HTML

coursid           coursename     teachername  stage     startdate
1                    x             pro1        1       2010/10/20
1                    x             pro3        2       2010/11/30
2                    y             proz        1       2009/11/10
2                    y             proy        2       2010/1/10  
3                    z             prow        1       2010/3/10
3                    z             pro4        2       2010/4/50
4                    d             pro5        1       2010/8/10
5                    f             pro6        1       2010/9/10

i want this table with query to sql

HTML

cousrid=1            teachername       stage      startdate
coursename=x            pro1             1        2010/10/20
                        pro3              2         2010/11/30
--------------------------------------------------------------
courseid=2             proz              1          2009/11/10
coursename=y           proy               2         2010/1/10
------------------------------------------------------------
courseid=3             prow              1          2009/11/10
coursename=z           pro4               2         2010/1/10
----------------------------------------------------------
courseid=4             pro5              1          2010/8/10
coursename=d           
 ---------------------------------------------------------------
courseid=5             pro6              1          2010/9/10
coursename=f           
 ---------------------------------------------------------------

Posted 4-Oct-14 2:04am

saeed1364

Add a Solution

Comments

george4986 6-Oct-14 0:05am

ur start date in table data and result doesn't match

4 solutions

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)

Afzaal Ahmad Zeeshan · Answer 1 · 2014-10-04T11:25:00

The only thing that you're doing is, that you're grouping the data on to the courseid and coursename. You can learn the GROUP BY from MSDN, http://msdn.microsoft.com/en-us/library/ms177673.aspx[^]

Second method (but I am not sure whether it will work in this or not) is the DISTINCT clause, that will select the distinct value records. Do give it a read, and see if it fits your needs. http://msdn.microsoft.com/en-us/library/ms132161.aspx[^]

The first bid would be the GROUP BY thing for SQL.

Side note: However, if I had to write these tables and do this application development myself. I would personally want to seperate those things. I would have created two tables, to minimize the repetition of the objects. In your table instead of repeating the courseid and coursename, create a new table and save the CourseID and CourseName there, and just use CourseID in this table. It will be better, and your data will be managed too.

George Jonsson · Answer 2 · 2014-10-04T17:09:00

Solution 2

As mentioned in solution 1, your table design is not very good.
You should look into how to normalize your database and redesign the structure.
It will make your life a lot easier in the long run.

Database normalization[^]

3 Normal Forms Database Tutorial[^]

Database Normalization Basics[^]

How to use a GridView in ASP.Net.
Accessing Data with the DataSource Controls[^]

Posted 4-Oct-14 17:09pm

George Jonsson

Updated 5-Oct-14 3:55am

v2

Comments

saeed1364 5-Oct-14 9:04am

I WNAT SHOW RESULT IN GRIDVIEW

George Jonsson 5-Oct-14 9:20am

Where in your question do you state that?
Should people guess what you want and just give it to you?
And never use capital letters. It is loud and rude.

saeed1364 5-Oct-14 9:26am

iam sorry
please help for question thanks

George Jonsson 5-Oct-14 9:56am

See the link in my updated answer.

Maciej Los · Answer 3 · 2014-10-05T07:51:00

In addition to solution 1 and 2 i strongly recommend to read about RDM[^] and RDBMS[^].

There should be at least 3 tables:
Course
CourseID
CourseName

Teacher
TeacherID
TeacherName

Courses
CourseID - ForeignKey, link to Course table
TeacherID - ForeignKey, link to Teacher table
Stage
StartDate
EndDate

Try to redesign database and use tips comprised in both solutions.

The final query should looks like:

SQL

SELECT c.CourseID, c.CourseName, t.TeacherName, m.stage, m.StartDate
FROM Courses AS m LEFT JOIN Course AS C ON m.CourseID = c.CourseID
    LEFT JOIN Teacher AS t ON m.TeacherID = t.TeacherID

And the most important thing: use UI rather than SQL to visualize data.
Best Practices in Data Visualization[^]

george4986 · Answer 4 · 2014-10-05T19:55:00

Hi Saeed,

Assuming ur table name as "tableTest" .
if u want course details in one row use this
-----------course------------
---coursid=1 coursename=x---

SQL

with temp as
(SELECT ('coursid = '+convert(varchar,coursid)+' coursename = '+coursename) as course,coursid, teachername,stage,startdate, row_number() over(partition by coursid order by coursid) rn
FROM   tableTest as b)
SELECT Case WHEN rn>1 THEN '' ELSE course END as course, teachername,stage,startdate,coursid 
from temp
order by  coursid,stage,teachername,startdate ;

if u want course details in two rows use this
-----------course------------
--- coursid=1 ----
--- coursename=x ----

SQL

with temp as
(SELECT ('coursid = '+convert(varchar,coursid)) as course1,+'coursename = '+coursename as course,coursid, teachername,stage,startdate, row_number() over(partition by coursid order by coursid) rn
FROM   tableTest as b)
SELECT Case WHEN rn>1 THEN course ELSE course1 END as course, teachername,stage,startdate,coursid  
from temp
union 
SELECT  course,null as teachername,null as stage,null as startdate,coursid  
from temp  group by coursid,course 
having count( coursid)=1
order by  coursid,course desc ,stage

good luck ;-)

how get this query for this table

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0