Need show 2 results from same column in same row

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

General SQL Server Forums

New to SQL Server Programming

Need show 2 results from same column in same row

Author

Topic

mstopkey
Starting Member

2 Posts

Posted - 2007-10-29 : 17:02:05

I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.

SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-29 : 17:06:03

Other than a missing comma between the column names and number column having quotes for values which is not required, what is the issue you have?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-29 : 17:06:42

And does it work?

Remove the single quotes around your intended column names.

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 17:10:09

Plus a slight risk that "name" and "number" are reserved words. Just putting that lot together you might like to try this:


SELECT [number] AS 'Customer',
       (case when seq = '0' then [name] end) as 'name1',
       (case when seq = '1' then [name] end) as 'name2'
FROM customer
WHERE [number] between '600080' and '600230'

Kristen

mstopkey
Starting Member

2 Posts

Posted - 2007-10-29 : 17:56:48

The problem is it was resticting the results to display only one customer.
BUT WAIT! I accidentally got it!
Added group by customer and it shows all!

Thanks for the replies. I usually dont get lucky and find it myself this soon.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 02:03:34

quote:
Originally posted by mstopkey

The problem is it was resticting the results to display only one customer.
BUT WAIT! I accidentally got it!
Added group by customer and it shows all!

Thanks for the replies. I usually dont get lucky and find it myself this soon.

How does adding a group by showed the correct result?
What is the datatype of number?
Why did you use single quote for alias names?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources