How to write sql query for this

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 2005 Forums

Transact-SQL (2005)

How to write sql query for this

Author

Topic

csri
Starting Member

45 Posts

Posted - 2008-07-04 : 17:05:12

Hi all
I have a table in sql server 2005.The table has three columns.The table data is as follows(1200 rows)
sample table data

ID Name City

1 Jack Paris

2 Don London

3 Jack London

NULL James Newyork

NULL Walt Newyork

4 Bill NewJersey

NULL Carol Newyork

My question is when I query using select statement the o/p must be like this.

Id Name City
1 Name paris
2 Don London
3 Jack London
James Newyork
Walt Newyork
4 Bill NewJersey
Carol Newyork

Is it possible to retrieve data like this using select statement.IF Yes please tell me how to acheive this

Thanks
sri

csri
Starting Member

45 Posts

Posted - 2008-07-04 : 21:09:33

Dear All
Is there any solution for the above problem.Please let me as I urgently need it.

Thanks
sri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-04 : 22:39:05

use isnull() function on the ID column

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-04 : 23:49:52

Hi khtan
Thanks for your reply.If I use ISNull() function on Id column .I get o/p as

Id Name City
1 Marsh paris
2 Don London
3 Jack London
0 James Newyork
0 Walt Newyork
4 Bill NewJersey
0 Carol Newyork

But I want output as.No zeros's or any other replacements are not acceptable to client.Is it possible in sql server 2005

Id Name City
1 Marsh paris
2 Don London
3 Jack London
James Newyork
Walt Newyork
4 Bill NewJersey
Carol Newyork

Thanks
sri

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-05 : 00:31:29

SELECT ISNULL(cast(ID as varchar(5)),''),NAME,CITY FROM < TABLE NAME>

ISNULL FUNCTION TAKES TWO ARGUMENTS

ISNULL( COLUMN,REPLACE VALUE)

IF COLUMNVALUE IS NULL IT IS REPLACED WITH 'REPLACE VALUE'..

KEEP THE REPLACE VALUE AS U WISH TO BE DISPLAYED

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-05 : 01:56:30

isnull(convert(varchar(10), ID), '')

KH
[spoiler]Time is always against us[/spoiler]

csri
Starting Member

45 Posts

Posted - 2008-07-05 : 09:41:19

Hi khtan
Thank you very much.Really appreciate your answer.That's what I was looking for.
Thanks
sri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources