querying for null - SQL Server Forums

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)

querying for null

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-23 : 05:55:45

Hi,

Ive got the following table

Field1, Field2, Field3
Argos, London, 4
Argos, Manch, 3
HMV, Mach, 6

How can I write a query to add another row HMV, London, value

case when(Field1 = 'HMV' AND Field2 <> 'London') THEN Value ELSE 0

That doesnt work as I thought it would

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-23 : 06:49:56

add another row or column ?

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

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-23 : 06:59:03

The table looks like this

Field1, Field2, Field3
Argos, London, 4
Argos, Manch, 3
HMV, Mach, 6

I want it to look like this

Field1, Field2, Field3
Argos, London, 4
Argos, Manch, 3
'HMV, London, [int]' <---- New row
HMV, Mach, 6

What could I do, what kind of query/logic?

quote:
Originally posted by khtan

add another row or column ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-23 : 07:14:34

like this ?


select Field1, Field2, Field3 from yourtable
union
select Field1 = 'HMV', Field2 = 'London', Field3 = 0

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

Kabila
Starting Member

33 Posts

Posted - 2009-09-23 : 07:36:54

create table t(field1 varchar(10),field2 varchar(10),field3 int)

SELECT DISTINCT a.field1,t1.field2
FROM ( SELECT DISTINCT field2
FROM T ) AS T1
CROSS JOIN T as A
LEFT OUTER JOIN T as b
ON A.field2 = b.field2
and A.field3 =b.field3
except
select field1,field2 from t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources