|
Hi All,
My database for student installment is like this.
ID StudentID Month Year Amount
1 001 4 2009 1000
2 001 5 2009 1000
3 001 6 2009 1000
4 001 7 2009 1000
5 001 8 2009 1000
6 001 9 2009 1000
7 001 10 2009 1000
Now i want the query to display it like
StudentID Apr2009 May2009 Jun2009 July2009
001 1000 1000 1000 1000
Any IDEA???
Plz share ur experiences.
Thansk
Yogesh Agarwal
|
|
|
|
|
If you using Sql Server 2005/8, have a look at the PIVOT command.
|
|
|
|
|
|
Check out the following link
PIVOT
|
|
|
|
|
hi everyone,
I have 2 tables like StudentDetail and TuitionLevel
StudentDetail
id Name Surname StudentUniqueId Subject
1 sam smith G1234U890 E
2 sam smith G1234U890 M
3 Paul Day R4321B009 E
E as English
M as Maths
TuitionLevel
id PreLevel PostLevel
1 3 4
2 5 4
3 2 2
I want to display result like this
Name MathsPre MathsPost EngPre EngPost
sam 3 4 5 4
Paul 2 2
I have done in the following way but this is not what i need.
SELECT [Name],Surname,StudentUniqueId,
Case when motot.Subject = 'E' Then 'English' Else 'Maths' end AS Subject,
Pre,
IsNull(Post,'') as Post
FROM StudentDetail sd
INNER JOIN TuitionLevel TL on TL.id =sd.id
ORDER BY StudentUniqueId
any help would be much appreciated.
thanks in adv.
modified on Wednesday, April 7, 2010 11:16 AM
|
|
|
|
|
Learner520 wrote: INNER JOIN TuitionLevel TL on TL.id =sd.id
What does the tuitionID have to do with the studentID? Also, what database are you communicating with?
If my answer has helped you, one of my articles may also be a help. Also remember that your best friend's name is google.
|
|
|
|
|
This article may be of use to you, assuming you are using SQL 2005 or later Pivot two or more columns in SQL Server 2005[^]
And yes that join is suspicious. Actually your data structure is screwed, not that you have sma smith stored twice, this is wrong. Suggested restructure:
Student
StudentID
StudentName
Subject
SubjectID
Subject
TuitionLevels
LevelID
Description
LinkTable
StudentID
SubjectID
LevelID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft Holmes,
Thanks for your reply. It was my mistake I didnot normaliz data but I did on purpose but it did not work.
Now I have normalized it and I'm trying to figure out how to display it through pivot.
But still need your help. Any suggestion or hint how to do that would be much appreciated.
thanks
TuitionLevel
tuitionlevelid tuitionleveldesc
1 1a
2 1b
3 1c
4 1
Student
UPN StudentName
A1234 Alan
A1233 Sam
A1232 Carol
Subject
Subjectid SubjectDesc
1 E
2 M
UPN subjid pretui_id posttui_id
A1234 1 1 2
A1234 2 3 4
A1233 1 7 3
A1233 2 1 4
A1232 1 5 6
A1232 2 6 6
output:
UPN StudentName PreMaths postMaths preEng PostEng
A1234 Alan 1a 1b 1c 1
Any help would be much appreciated.
thanks
|
|
|
|
|
If you work through the article I basically do it in 2 steps. Build up the query that will service the pivot.
Get your student details, the course details and your performance information into a normal result set, do not get any extra data like ID information that is not needed.
Get a distinct list of the course names (either from the result set or all the courses), these will be your pivoted columns.
I would do a hard coded mini query to get a feel for it, once you get a result by hard coding the pivot columns you can turn it into dynamic sql. To debug the dynamic sql print the text and try and run that.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hy,
While using pivot in my stored procedure i got the following error
"Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel."
To Solve it when i used "EXEC sp_dbcmptlevel database, 90" my sp got execited sucessfully
but the rest of the sp's in the same database and having pivot stared showing me error
After i changed it back to "EXEC sp_dbcmptlevel database, 80" the rest of the sp's havng pivot started working properly except the current sp i am working on.
Kindly help,
|
|
|
|
|
Hy,
I have got 2 servers(sqlserver 2000 and sql server 2005) is there any way so that i can fetch the records of table from ql server 2000 from sql server 2005 database
|
|
|
|
|
You can link the servers[^]. This way you can "attach" a Sql-database that's located on another server.
I are Troll 
|
|
|
|
|
If you dont have permision to create a link server, you can extract the data to a file from the table in Server B and then import them in Server A.
if you need help extracting and importing, read about BCP and Bulk Insert or even DTS
|
|
|
|
|
Hi All,
The project in which i am working needs to be multilingual.
I need to insert and retrieve data in these languages :
1. Tamil
2. Telugu.
3. Malyalam.
4. Oriya.
5. Kannada.
Actually we don't know which font to use for which language.
Do I need to install fonts on the server or is there any patch need to install??
Please help me out of this!!
Thx & Rgds
Aman
|
|
|
|
|
Actually I think you need to install the fonts on the client, I'm not sure as we only have to support 2 languages and did not need to install the chinese fonts on the server.
When you create your columns use nvarchar and not varchar. Remember to double the size of the text field when using nvarchar.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I do not think that that is a problem. For text fields, we use "nvarchar" so that we can cope with the German ÄäÖöÜüß.
I copied some text from a web site in Hindi language into some text fields of our application. The data can be retrieved correctly - even with a SQL Server 2000.
|
|
|
|
|
I am trying to create a new tab in the Ribbon of my Access 2007 application (a new menu-item in the menu bar for us old tymers...)
I have followed the example at the MSDN site:
http://msdn.microsoft.com/en-us/library/aa338202.aspx#OfficeCustomizingRibbonUIforDevelopers_Access[^]
and it does create a Custom Tab with a custom group within.
However
1. I don't know how to include my own sub-items (Help, About)
2. I have a side effect for dialog-type forms:
If i open a form of this type, right-click and select "Close Alt+F4"
that window turns to the view listing (Design View, Layout View, Form View, Datasheet View
and also Save, Close, Close All)
Is this "normal" - because I see it before I added my Ribbon stuff
3. I want to display a compiled help (.chm) file
I just want to add a custom tab that allows shows my own About form and maybe compiled Help (.chm) file.
Thank you,
JJ
modified on Wednesday, April 7, 2010 11:33 AM
|
|
|
|
|
I'm hoping this is the appropriate board for my question. If not I apologize in advanced. My company is using MS Access to store all certificates we issue. Part of the form consist of a combo box with all the inspector names and then a text box next to it with there license number. I wrote some code so that when an inspector's name is selected it first checks if their license is expired. If it is, it displays a warning message. If it's not expired, it then places their license number in the text box. Everything was working fine.
However, now when a user opens the form, if the first thing they do is change the combo box to a different inspector, the VBA code with opens up and breaks at the first line of code. If the code is let to continue, it will not break again until the database is reopened. Now, if any other field is changed first, and then the inspector's name is changed the code does not break. Why is the code breaking only on this subroutine and only if it is the first field changed? There are no break points set, and I couldn't find anything in the properties windows and nothing online. Any help would be greatly appreciated as I'm at a total loss of what to do.
|
|
|
|
|
Could you post the code for the event handler ?
Is it the On Change Event ?
Also post the exact error message that is being displayed.
I'm guessing that there is a reference to something that is not initialized which is causing the error.
I'll do my best to help you.
|
|
|
|
|
|
Don't cross post: you have the exact same question in the c# forum. Delete one or the other before they both get removed.
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Typically, you connect an application to a database server using a "connection string". This connection string contains the NAME of the server, not the IP address; by doing this your network administrator could change the IP address of your server and your application would not have to change. A DNS server can be used to provide the layer of abstraction between an IP address and the name of a server.
Refer to:
DNS Defined: http://technet.microsoft.com/en-us/library/cc787920(WS.10).aspx[^]
Connection Strings: http://www.connectionstrings.com/[^]
|
|
|
|
|
Until the DNS server dies and your app won't run until it's replaced.
I wrote my own little network name cache/resolve system to handle that problem after I got bit.
|
|
|
|
|
Hi
I want to send Email to particular users stored in table at a regular interval like 1 hours. I will buy a mail server. I want to know is it possible to do this using sql server. If yes then what approach should I follow to implement this.
Thanks in advance
|
|
|
|
|
There are lots of approaches
here is but one :-
Write a .NET Service That
1. Defines a Timer for whatever interval - eg 60 minutes
2 Every interval/timer event, read the database table
3. For every row in the selected users set from the database
3a. construct the email
3b. send email using whatever protocol is applicable for your mail server
'g'
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
