|
And I have a question in biology that needs a response for, can you do the needful for that one as well.
This will be pay back when the idjit ends up working with you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Respected Sir,
Hope you are doing well.
The question being asked can be solved in 2 ways, as per my understanding.
#1: If the data storage format is like StudentRoll,Name,English,Physics,Biology,Maths etc. (which he didn't asked )
In this case, in-order to flatten the data, first we will use UNPIVOT . Once done, the next step will be to use Row_Number() and get the N-th highest marks. Finally , to get the original structure back, we should apply PIVOT.
#2: If the storage format is like SName,Subject,Marks(which he asked )
In this case, since the data is already flattened by Subjects, we must apply the RANKING function (as I applied Row_Number()) here and get the N-th highest one.
Kindly let me know, if my understanding of your question is correct in which case please provide the feedback of the response.
|
|
|
|
|
My apologies, I was being sarcastic. The point I was trying to make is that Chris had given him the pointers where he could research and learn how to do the job. Whereas you gave him the solution, which does not help him learn.
If it was a difficult question then the example may have been the best way to answer. As it is an extremely basic question the OP needs to do some learning/research/school work rather than cut and paste your answer.
Asking someone to "do the needful" is just plain rude.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am using the following script to transpose a particular column row as columns, but the problem is I am able to get only one row, for example I have StudentNames, Ids, AggregateMarks, and TotalMarks.
If I use the below approach and generate a column for every student then I am able to get only TotalMarks for each student, but I want to get Ids, AggregateMark also under each studentName
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from yourtable
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT ' + @cols + N' from
(
select value, ColumnName
from yourtable
) x
pivot
(
max(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
Can anybody please help me in this, any code snippet or suggestion or a link would be very very helpful, please thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You could have a look at this[^]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I have Printed the dynamic query, it is as below. But same thing how to get that ServiceLevel from the below query, it is printing only Service id for each column in a row, but I want Service Level also in another row, can't we do it? Any sort of help would be very very helpful, please help me I am also trying my best. Thanks in advance.
SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from
(
select ServiceLevel, ServiceId, Name
from [Service]
) x
pivot
(
max(ServiceId)
for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
[Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
)
p
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
@indian1433, here is a way (The first version is static)
DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)
SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames
IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id = X.Id
ORDER BY 1
I have done this one to show you how it must go like. The dynamic version follows
CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from #T
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames IN (' + @cols + N')
) AS X
JOIN #T t1 on t1.Id = X.Id
ORDER BY 1'
print @query
exec sp_executesql @query
DROP TABLE #T
Hope this helps.
modified 31-Oct-15 2:14am.
|
|
|
|
|
|
Linking to an article on your own blog, without posting the relevant details here, is considered spam.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Okies... Thanks for the information...Will take care...
|
|
|
|
|
Hi,
I am wondering why I am getting error when running below simple IF in MySQL?
IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16');
END;
END IF;
This is the error I am getting:
[SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time) VALUES (10, '2015-10-11 13:14:16');
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE empl' at line 1
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
..probably because you are showing a query for SQL Server. The error says to look up the correct syntax[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
Hi All,
We are moving the data from a huge database into another database as we guess we have Duplicate values on Name and Description fields in some tables we want to list our all those Tables which have these columns and duplicates on those columns using some script dynamically.
Is there any way to write that sort of script if some body already did it and can help me, it would be a great help I am also searching any link, suggestion or code snippet would be a great help.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
How to do it would depend a lot on the database. You should update your post with that info.
|
|
|
|
|
anyways is fine it is just an internal running script but I want to run it on all tables in the selected database on selected server. I though of little bit with dynamic sql but yes that's question too, is there any better approach for it than dynamic sql?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
@indian1433, you can try with sp_MSforeachdb and sp_MSforeachtable .Please check out and let us know if the hint has helped you.
N.B.~ Since you mentioned that it's just for internal purpose, so I have mentioned those two undocumented Stored Procedure. Please don't use it in production since they r undocumented and there is no guarantee that in future MS will not remove them (:
|
|
|
|
|
Hi All,
I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children.
How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows.
I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way
SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=D:\Servxxx.xls',
[Servxxx First Sheet$]);
It is giving me the following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
It is saying file is not there, but file is there and it is erroring out with link server etc.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Is D: a local drive, or a mapped network drive?
Does the SQL service account have permission to access the file?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hey Rick, thanks I got it sort out my friend here is the sample one may be somebody has the same issue.
SELECT * INTO #xxxxxxs
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=C:\xxxxxxx\Dont Remove ThisFolder\xxxxxxs.xlsx',
'SELECT * FROM [Back xxxxx$]');
I have a question here, do we have any way to copy the existing schema structure into a table variable, along with an identity column?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I am using Sql server 2008.
I have 3 columns say, UserID,ManagerID,deptCode.
I need the output as UserID,ManagerID,deptCode,ManagersDeptCode
So, the column "ManagerID" need to be used to generate the output of "ManagersDeptCode".
Please help.
|
|
|
|
|
Join back to your user table using the managerid ot userid to get the managers details
Select U., M.
From UserTable U
inner join UserTable M on M.userID = U.ManagerID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a table A in it I have columns like Id, ParentId, ParentName, Child1Id, Child1Name, Child2Id, Child2Name. ParentId is the parent of Child1Id, and Child1Id is the parent of Chil2Id. Id is the Primary key of this table.
I want to have a select statement which, I have to get ParentIds, ParentNames order by ParentNames, under each Parent, I want to have Child1Ids, Child1Names, order by Child1 Names, Under each Child1s, I want to have its child2Ids and Child2Names.
ParentId, ParentName
Child1Id, Child1Name
Child2Id Child2Name
Child2Id Child2Name
Child2Id Child2Name
Child1Id Chil1Name
Child2Id Child2Name
Is there any way to write a query like this? Please help me, any type of help, a link, a code snippet or a suggestion would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
