|
I must be missing something - surely, if you partitioned by both X and Y, you'd end up with the Cartesian product of the two tables? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well, that, is exactly my problem! 
|
|
|
|
|
The data turned out to be a lot less ordered or rather more asymmetrical than expected (just like I should've expected  )
And the mapping between the databases also turned out to be important in only one direction.
So I ended up using a variant of your solution, but using RANK instead of ROW_NUMBER.
Now I'm working on refining the ranking function for whenever I get duplicate matches
|
|
|
|
|
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)
// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);
I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data.
Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pv
modified 12-Jun-16 9:19am.
|
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
