|
You have a problem with your Unpivot - try removing 'Student' from the column list i.e.
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))as upv
There is also a problem with the Pivot - you need to use a summary function e.g.
Pivot(MAX(course) for Student in (Jane, Michelle, Dan)) as pv
Unfortunately if you fix those two problems you get the following results
ID Courses Jane Michelle Dan
1 Engineering A NULL NULL
2 Engineering NULL A NULL
3 Engineering NULL NULL B
1 English A NULL NULL
2 English NULL A NULL
3 English NULL NULL A
1 Mathematics A NULL NULL
2 Mathematics NULL B NULL
3 Mathematics NULL NULL A
1 Physics B NULL NULL
2 Physics NULL A NULL
3 Physics NULL NULL A
This is where the article mentioned by @Mycroft-Holmes comes into play OR you can fiddle it like this
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
|
|
|
|
|
Thank you so much for replying. Your solution is 98 percent there, the only problem is that the courses in the result are listed out of order.
In my scenario the order of the courses in the result is trivial. However, in other cases where I have to let's say show companies' earnings by quarter, show companies' stocks by quarter, etc., it will be important.
How can I modify my solution to show the courses in their original order? Thanks again for your help.
modified 12-Jun-16 14:49pm.
|
|
|
|
|
The only solution that comes to mind at the moment (I'll keep thinking!) is to have a separate table listing the courses with an Id field that reflects the order you require e.g.
DECLARE @courseNames table (id int identity(1,1), course nvarchar(25))
insert into @courseNames values ('English'),('Physics'),('Mathematics'),('Engineering')
It could just as easily be a permanent table as a table variable. You could use such a table to generate dynamic SQL for the PIVOT if more courses are added).
You can then join the previous query to this table and ORDER BY the id on the course table e.g.
select cn.id, Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
inner join @courseNames cn ON pv.Courses=cn.course
GROUP BY cn.id, Courses
ORDER BY cn.id or if you don't want to include the ID number in the output you could use a CTE ...
;with q as
(
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
)
select q.Courses, q.Jane, q.Michelle, q.Dan
FROM q
inner join @courseNames cn ON q.Courses=cn.course
ORDER BY cn.id
|
|
|
|
|
Thank you for all your help, your solution using table variable works like a charm.
|
|
|
|
|
|
Psst! Enhance his rep by upvoting his response, green angle on the left!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi ,
I am getting this message on SQL log but i am not initiated any SQL job maintenance.
Please help me what this msg indicates.We are taking Drive backup.
Msg:
Database backed up. Database: abc, creation date(time): 2015/04/17(08:07:47), pages dumped:56589001, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{622759E005}7'}). This is an informational message only. No user action is required.
Thanks,
Sree
|
|
|
|
|
When you take a backup of your disk, you can't just take a copy of the SQL database files. If you did, you could end up with an unusable backup.
Instead, the backup product uses the Volume Shadow Copy Service to communicate with SQL Server. SQL then initiates a proper backup of the databases, writing them out to a virtual device that represents the target of the backup job.
This is perfectly normal, and nothing to worry about.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I am trying to creat a simple IF - THEN in MySQL but getting a synax error message.
can anyone help please...
here is my statement:
IF EXISTS (SELECT payroll_id FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year) THEN
SET param_payroll_id = 0;
END IF;
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Never used MySql but based on my search, you can't use that syntax in Mysql. Alternate way is, just store the result of query into some variable & use it for comparison to achieve the output. Check this sample
MySQL Stored Procedures IF NOT EXISTS()[^]
BTW, always include the complete error message(just 'Syntax error' is not enough) in your question which is better for members to give you answers quickly.
|
|
|
|
|
I tried below code but getting same error for the IF--THEN
SET param_payroll_exists = (SELECT COUNT(*) FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year);
IF EXISTS (param_payroll_exists > 0) THEN
SET param_payroll_id = 0;
END IF;
the exact error is:
You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server for the right syntax to use near 'param_payroll_exists > 0) THEN ..........
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: IF EXISTS (param_payroll_exists > 0) THEN
Do you really mean EXISTS here?
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
You didn't get Peter's reply which's fair enough? Just remove EXISTS from that query & run. It's over.
|
|
|
|
|
Hello,
I am using SQL server 2008 r2. I am new to SQL triggers and trying to call dll from SQL triggers. I have created dll using vb.net,
Public Class Class1
Public Function abc(ByVal a As String) As String
Return a.ToUpper
End Function
End Class
I have successfully added assembly to the SQL using,
CREATE ASSEMBLY triggertest
FROM 'D:\dll\triggerdll.dll'
WITH PERMISSION_SET = SAFE
and then added a trigger using,
CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.Class1.abc
Here I am getting error,
Msg 6505, Level 16, State 2, Procedure tri_add_clr, Line 1
Could not find Type 'Class1' in assembly 'triggerdll'.
where triggerdll is the name of dll.
How to solve this error ?
Am I calling dll from SQL trigger properly ?
Please help.
modified 1-Jun-16 9:55am.
|
|
|
|
|
VB.NET projects have a root namespace[^]. Unless you've cleared that in your project's properties, your class exists within that namespace, and you need to include it within the EXTERNAL NAME specification:
CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc
Also:
srikrishnathanthri wrote: CREATE ASSEMBLY triggertest
FROM 'D:\dll\Interop.ADODB.dll'
WITH PERMISSION_SET = SAFE
Are you sure that's the correct assembly? That looks like an interop assembly for the ancient "ADODB" library to me.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Tried your solution,
Richard Deeming wrote: CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc but I am again getting error,
Msg 6573, Level 16, State 1, Procedure tri_add_clr, Line 1
Method, property or field 'abc' of class 'ClassLibrary1.Class1' in assembly 'triggerdll' is not static.
Sorry there is a mistake in question about dll name, I have edited that.
|
|
|
|
|
|
Hello everybody!
I have three tables, namely t1, t2, t3
Dim cmdText As String
cmdText = "SELECT t1.StaffName, t2.CampusName
FROM t1
INNER JOIN t2
ON t1.CampusID = t2.CampusID
ORDER BY t1.StaffName"
It is working fine.
My problems starts when I want to include t3; such that;
t1.StaffID = t3.StaffID
I don't know; how to get it done. Please note that; StaffID is the Primary Key of t1 and the Secondary Key of t3.
Would anyone help me, please!
|
|
|
|
|
Could you describe the structure of those three tables? We'd usually join between a primary and a foreign key, not a secondary one.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Are you just looking for the syntax to join another table?
SELECT
...
FROM
t1
INNER JOIN t2
ON t2.CampusID = t1.CampusID
INNER JOIN t3
ON t3.StaffID = t1.StaffID
ORDER BY
...
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much, Richard Deeming, for the help.
Have a very good day.
|
|
|
|
|
Hello,
I have a SQL table with some existing data. In that I have a columns named "Name" and SL No (which is a primary key). For example say I have one entry for that column as "Thilak". Suppose if a next entry comes with same name i.e "Thilak", I want to rename the existing "Thilak" as "Thilak_Slno" and new entry as "Thilak_Slno". And If again any Thilak repeats I want append serial number to that before saving or soon after saving to the database.
The purpose is simple, I am giving option to post a query in my software based on username. while selecting the name, if same name exists in entry that will be difficult to select the Name, if name comes with serial number it will be easier.
So whenever a new entry comes which is same as the existing name, I am planning to append the serial number to the existing names also to the incoming name.
So, now whether it is possible to change the name as it enters into the table ? or should I change once it get saved to database ?
How to do this ?
Please help me.
|
|
|
|
|
If you want all names to be unique, then just add the serial number in the first place. Then you do not need the extra steps of searching for an existing name and changing it when adding to your database. Alternatively just accept that there may be duplicates which you can process when searching.
|
|
|
|
|
You shouldn't change it at all. They are two separate facts, and should each be in their separate field. That way you don't need to "substring" on the name when the numbering changes, and can easily add another field if your combination is no longer unique.
The database is not there to store "formatted" values, but data. Upon retrievel, you paste to two fields together and have what you want.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I am using SQL express 2008 r2. Some times while running SQL backup query,
Text
BACKUP DATABASE [DBName] TO disk = 'TargetPath' WITH CHECKSUM
I am getting the error 'detected an error on page'. But if I run query without checksum then it runs successfully.
My question is,
1. Why that error is coming ?
2. What should I do if that error comes ?
3. Can I rely on the backup without checksum ?
Please help me.
|
|
|
|
|