Multiple columns to 1 ordered column??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple columns to 1 ordered column???

Author

Topic

Ic0n
Starting Member

18 Posts

Posted - 2001-12-31 : 13:22:54

Hi,

Need some help with a query. I have a table full of names with the following structure :-

Company_Name
FirstName
LastName

If I enter a company the first and lastname fields are blank, if i enter a person the company field is blank.

What i need to do is query the table and return 1 column in alphabetical order with non-companies written as 'Lastname, FirstName'

Is this possible?

Thanx

mfemenel
Professor Frink

1421 Posts

Posted - 2001-12-31 : 13:27:46

select firstname + ', ' + lastname from tablename
--You can adjust the ordering as needed
order by lastname,firstname

Mike
"A program is a device used to convert data into error messages."

Edited by - mfemenel on 12/31/2001 13:28:05

Nazim
A custom title

1408 Posts

Posted - 2001-12-31 : 14:15:10

if you mean by non-companies the companies where no entries are made then a where clause on company is needed on mfemenel's query

select firstname + ', ' + lastname from tablename
where company is null
order by lastname,firstname

[quote]
What i need to do is query the table and return 1 column in alphabetical order with non-companies written as 'Lastname, FirstName'
[quote]

HTH

Ic0n
Starting Member

18 Posts

Posted - 2001-12-31 : 14:55:47

Thanx, that works great but is it possible for me to have the Companies in the same column and still have it ordered alphabetically?

Nazim
A custom title

1408 Posts

Posted - 2001-12-31 : 15:00:54

yeah, you just need to add the column

select firstname + ', ' + lastname +','+ companyname
from tablename
order by lastname,firstname,companyname

i dont think you need to check any condition in this.

HTH

daKarateKid
Starting Member

15 Posts

Posted - 2001-12-31 : 15:16:49

select lastname + ', ' + firstname as contactname
from contact
where company is null
union
select company as contactname
from contact
where company is not null
order by contactname

I like this solution a little more. The reason is that ANSI NULL concatenated with anything else is by default also NULL. See SET CONCAT_NULL_YIELDS_NULL in BOL.

dKK

Nazim
A custom title

1408 Posts

Posted - 2002-01-01 : 00:15:49

select ltrim(isnull(lastname,' ') + ', ' + isnull(firstname,' ') +'--'+isnull(company,' '))
from contact
order by lastname,firstname,company

should do it

Edited by - Nazim on 01/01/2002 04:38:56

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-01-01 : 11:08:01

Yet another way!


set nocount on

create table #tmp (CompanyName varchar(100)
 , FirstName varchar(30) , LastName varchar(50) )


insert #tmp (CompanyName , FirstName , LastName )
values ('The Smith Group' , null , null )
insert #tmp (CompanyName , FirstName , LastName )
values ('Watson and Watson' , null , null )
insert #tmp (CompanyName , FirstName , LastName )
values (null , 'Liz' , 'Kelmin' )
insert #tmp (CompanyName , FirstName , LastName )
values (null , 'Michael' , 'Allen' )
insert #tmp (CompanyName , FirstName , LastName )
values (null , 'Bob' , 'Zane' )


select Contact = Case
       when CompanyName is null 
       then LastName + ', ' + FirstName
       else CompanyName
       end
from #tmp
order by 1

drop table #tmp

Results:


Contact
------------------------------
Allen, Michael
Kelmin, Liz
The Smith Group
Watson and Watson
Zane, Bob

Ic0n
Starting Member

18 Posts

Posted - 2002-01-01 : 18:10:35

Thanx for all the replies :)

I'll have a play with all the suggestions and see what works best with my app

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-04 : 08:19:31

How about using coalesce?

select coalesce((LastName+','+FirstName),Company) from table

Coalesce will return the first non-null argument.

You could order by the coalesce if you want the entire list ordered alphabetically, or by company,LastName,FirstName if you want the companies separate to the individuals.

Coalesce is very handy for many things and is worth reading up on in BOL.

============
The Dabbler!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources