June 15, 2012 at 10:55 pm
Hi i have small doubt in sql server.
supooste a table data contains like
id , name , sal , deptno
1 , abc , null , null
null , null , 100 , 10
2 , deg , null , null
null , null , 200 , 20
3 , hij , null , null
null , null , 300 , 30
so i want out put like
id , name , sal ,deptno
1 , abc , 100 ,10
2 , def , 200 ,20
3 , hij , 300 ,30
how to write query to get output.plz tell me the query
June 15, 2012 at 11:41 pm
asranantha (6/15/2012)
Hi i have small doubt in sql server.supooste a table data contains like
id , name , sal , deptno
1 , abc , null , null
null , null , 100 , 10
2 , deg , null , null
null , null , 200 , 20
3 , hij , null , null
null , null , 300 , 30
so i want out put like
id , name , sal ,deptno
1 , abc , 100 ,10
2 , def , 200 ,20
3 , hij , 300 ,30
how to write query to get output.plz tell me the query
If i change the order of the rows in your example, can you explain to me which rows to combine to produce the output you desire?
id , name , sal , deptno
3 , hij , null , null
null , null , 100 , 10
2 , deg , null , null
null , null , 200 , 20
null , null , 300 , 30
1 , abc , null , null
If you can't, then your question is impossible. Tables don't guarantee any order unless you use an ORDER BY clause.
June 16, 2012 at 3:54 am
It's very peculiar data too. Why would you have NULLs for the ID and name on rows in the same table where the department and salary clearly matter? Your task would be much easier if you sorted out the data and updated each employee ID with the required DeptID and Salary information.
June 16, 2012 at 11:36 am
asranantha (6/15/2012)
Hi i have small doubt in sql server.supooste a table data contains like
id , name , sal , deptno
1 , abc , null , null
null , null , 100 , 10
2 , deg , null , null
null , null , 200 , 20
3 , hij , null , null
null , null , 300 , 30
so i want out put like
id , name , sal ,deptno
1 , abc , 100 ,10
2 , def , 200 ,20
3 , hij , 300 ,30
how to write query to get output.plz tell me the query
You can't.
Jared
CE - Microsoft
June 17, 2012 at 5:32 am
iam traying the query like this way
select a.id,a.name,b.sal,b.deptno from(select a.id,a.name,row_number()over(partition by id order by id)as c from tablename a
)
inner join
select (b.sal,b.deptno,row_number()over(partition by sal order by sal)as d from tablename b))j
on a.c=b.d
BUt what ever i except that output not getten.my way is first we think table dived 2 parts and we create rownmber that time we apply inner join that time what ever matches that records we can retrive.
but small errore occured this query .plz implment this query to get output.
June 17, 2012 at 10:46 pm
You can do it as follows, but the results would vary according to the Order in the chosen Execution Plan.
The results may not be universal in this case as there is no Logic behind it.
--Creating Table
Create Table Ex
(id int,
name Char(3),
sal int,
deptno int )
--Inserting Sample Data
Insert Into Ex
Select 1 , 'abc' , null , null
Union ALL
Select null , null , 100 , 10
Union ALL
Select 2 , 'deg' , null , null
Union ALL
Select null , null , 200 , 20
Union ALL
Select 3 , 'hij' , null , null
Union ALL
Select null , null , 300 , 30
--Query For Your Requirement
Select a.id, a.name, b.sal, b.deptno From
(Select *, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From Ex) As a
Left JOIN
(Select *, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From Ex) As b ON b.rn = (a.rn + 1)
Where a.id IS NOT NULL
June 19, 2012 at 3:42 am
If it can be made to fit into your workflow, my inclination would be to create some kind of script to preprocess the file into this:
id , name , dummy1 , dummy2, dummy3, dummy4, sal, deptno
1 , abc , null , null, null , null , 100 , 10
2 , deg , null , null, null , null , 200 , 20
3 , hij , null , null, null , null , 300 , 30
Then the SQL side of things becomes trivial.
June 19, 2012 at 4:58 am
Andrew Watson-478275 (6/19/2012)
If it can be made to fit into your workflow, my inclination would be to create some kind of script to preprocess the file into this:id , name , dummy1 , dummy2, dummy3, dummy4, sal, deptno
1 , abc , null , null, null , null , 100 , 10
2 , deg , null , null, null , null , 200 , 20
3 , hij , null , null, null , null , 300 , 30
Then the SQL side of things becomes trivial.
Who said it was a file? 🙂 I think this is a homework question, and unless you know that the sal of 100 has an id of 1, and sal of 200 has an id of 2... i.e. some sort of matching pattern (which I assume does not make exist since there is a name associated with it), this cannot be done logically. If you don't care about logic, which again makes no sense, then it can be done as stated above.
Jared
CE - Microsoft
June 19, 2012 at 5:09 am
Who said it was a file?
Yep - don't know where I got that from. I guess it just looks like the sort of thing that's come from a file (I've been doing a lot of SSISing of csvs lately ;-)).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply