I want the output as follows

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

XML

I have a table as following

Employeeid     Employeename                      Relation ship
16         Malliah                             Self
25         vishnu                             Son
25         Narayana Murthy Konathala      Husband
25         Tulasi                              Self
16         NageswaraRao                        Father
16         Vivek                                Son

<b>
And i want the output as follows</b>

Employeeid     Employeename                      Relation ship
25         Tulasi                              Self
25         vishnu                             Son
25         Narayana Murthy Konathala      Husband
16         Malliah                             Self
16         NageswaraRao                        Father
16         Vivek                                Son


First the sekf relation must be present and in the next rows their relations must have to come as like first self and then next father, husband like that

Posted 23-May-13 20:29pm

Member 9848727

Add a Solution

3 solutions

Solution 1

Consider to add hierarchyid column if it possible, update values in this column with respective weights and construct your queries.
This link could be helpful: Tutorial: Using the hierarchyid Data Type[^]

Posted 23-May-13 20:39pm

skydger

Solution 3

SQL

create table emp(Employeeid  int, Employeename nvarchar(30), [Relation ship] nvarchar(50))


insert into emp values(16, 'Malliah', 'Self')
insert into emp values(25, 'vishnu', 'Son')
insert into emp values(25, 'Narayana Murthy Konathala', 'Husband')
insert into emp values(25, 'Tulasi', 'Self')
insert into emp values(16, 'NageswaraRao', 'Father')
insert into emp values(16, 'Vivek', 'Son')


select Employeeid, Employeename, [Relation Ship] from
(
select Employeeid, Employeename, [Relation Ship],
CASE 
WHEN [Relation ship]= 'Self' THEN 1 
WHEN [Relation ship]= 'Father' THEN 2 
WHEN [Relation ship]= 'Husband' THEN 2 
WHEN [Relation ship]= 'Son' THEN 3 
END Col4
from emp 
)x order by Employeeid, Col4 ASC

RESULT

Employeeid Employeename Relation Ship
----------- ------------------- ----------------------------------------
16 Malliah Self 1
16 NageswaraRao Father 2
16 Vivek Son 3
25 Tulasi Self 1
25 Narayana Murthy Konathala Husband 2
25 vishnu Son 3

(6 row(s) affected)

Posted 23-May-13 21:04pm

Arun Vasu

Updated 23-May-13 21:11pm

v2

Comments

Member 9848727 24-May-13 3:08am

i dont want the last column from the output which you hace given to me
@@Arun Vasu

Arun Vasu 24-May-13 3:12am

now check it.. my updated answer

Member 9848727 24-May-13 3:16am

ya its working thanku..

@@Arun Vasu

Arun Vasu 24-May-13 3:17am

close this discussion by mark as answer.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-05-23T20:42:00

Solution 2

Try this...

SQL

select  
Employeeid, Employeename, [Relation ship]
From TableName
Order By EmployeeId desc,
case when [Relation ship] = 'self' then 1 else 2 end

Happy Coding!
:)

Posted 23-May-13 20:42pm

Aarti Meswania

Updated 23-May-13 21:14pm

v3

Comments

Arun Vasu 24-May-13 2:56am

but output is not correct or what he want?

Aarti Meswania 24-May-13 3:01am

check now :)

Member 9848727 24-May-13 2:58am

Only 'self' must have to come first and then next any relation any can come...

If i use the above code it is displaying as 1,2,3,4,5 under the column relationship

Aarti Meswania

Aarti Meswania 24-May-13 3:01am

check updated solution :)

Arun Vasu 24-May-13 3:07am

NO still its not correct. check my solution

Member 9848727 24-May-13 3:06am

If im using in this way im getting '1' for the 'self' and 2 for the remaining relations

@@Aarti Meswania

Arun Vasu 24-May-13 3:07am

check my solution.. is it correct? its correct then mark as answer

Aarti Meswania 24-May-13 3:14am

check now

Arun Vasu 24-May-13 3:17am

now exactly correct.. well done.. keep it up

Aarti Meswania 24-May-13 3:19am

yes I forgot employeeid in orderby clause, thanks for make me remember :)