How to Show all values in a single column

[M.Waqas Aslam]

hello !
may God bless all of you, i m good in mssql , but this is very first time that i am very confuse , the case is , i have a table , having fields
1- ContactPersonId
2- Name
and 2nd table having fields
1- recId
2- ContactPersonId
3- PhoneNo
now i want to show all this information in my grid like in this way
abc ----- this is name
123 ----- this is number
456 ----- this is number
4785----- this is number
xyz----- this is name
145----- this is number
658----- this is number
mno----- this is name
852----- this is number
963----- this is number
please some one help me in this , or if you dont know how to do this then please share your ideas that how you think it can be done.

Best Regards

[poojavb]

Union shud work and give the same alias name for all...

SELECT DISTINCT FirstName as Name From HMS.dbo.DoctorRegister
UNION
SELECT DISTINCT LastName AS Name FROM HMS.dbo.DoctorRegister
UNION
SELECT DISTINCT FName As Name FROM HMS.dbo.InpatientDetails

hope this is what u need...

[adam_k]

I'm going to go with UNION, but not like poojavb. I'm guessing that you want the numbers to go under the person's name and poojavb's script won't do that.
Instead I'm thinking:

select Name as 'a' , Name from Contacts --change table name
union 
select Name, PhoneNo from Contacts inner join PhoneNums 
on Contacts.ContactPersonId = PhoneNums.ContactPersonId 
order by a

[M.Waqas Aslam]

thanks both of you , but prob is same , but thanks for your valuable time , :)

Regards

[M.Waqas Aslam]

any one help me in this , :(

[adam_k]

I've tested my query and it was messing up the order of the records displayed. As a fix I've introduced a second field to help order the results properly.

select Name as 'a',1 as 'b', Name from Contacts --change table name
union 
select Name, 2 , PhoneNo from Contacts inner join PhoneNums 
on Contacts.ContactPersonId = PhoneNums.ContactPersonId 
order by a,b

The only drawback is that you can't use this as a sub-query to eliminate columns 'a' and 'b'. Instead if you can't handle it with in your front-end, you have to insert into a temp or variable table and select out of that.

Let me know how this works for you.

[BitBlt]

You kind of have to do this in two parts. First, select data from the two tables into a temp table, then use the temp table to select and sort

Try this:

select '1' as tblName, ContactPersonId, [Name] 
into #temp1                         -- here is where the temp table is created
from contactperson 
union 
select '2', ContactPersonId, PhoneNo from ContactPhone

select #temp1.name
from #temp1
order by ContactPersonId, tblName   -- here is where you sort the data the way you need it

drop table #temp1                   -- be sure to clean up after you're done

Of course, I made up table and column names, but you get the picture. You might also consider putting it in a stored proc, but I'll leave that to you.

Good luck!

[M.Waqas Aslam]

thanks all of you , i just manage to do it in other way using dataview control in ExtJs. thanks for your time and help . God bless you in your lifes.

Regards