please rectify my problem - 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

General SQL Server Forums

New to SQL Server Programming

please rectify my problem

Author

Topic

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-10-07 : 01:34:39

this is my query:
select a.tech_process,a.tech_mech,a.tech_instrument,a.commercial,a.budgetary from(select case when tech_process= 'sasi' and q_no='q5' then 'tech_process' end as 'tech_process',case when tech_mech ='sasi' and q_no='q5' then 'tech_mech' end as 'tech_mech',case when tech_instrument='sasi' and q_no='q5' then 'tech_instrument' end as 'tech_instrument',case when commercial='sasi' and q_no='q5' then 'commercial' end as 'commercial',case when budgetary='sasi' and q_no='q5' then 'budgetary' end as 'budgetary' from quotation where tech_process='sasi' and tech_mech='sasi' or tech_instrument ='sasi'or commercial='sasi' or budgetary='sasi') a where a.tech_process is not null or a.tech_mech is not null

the output of this query is :

tech_process tech_mech tech_instrument commercial budgetary
---------------------------------------------------------------------
tech_process NULL tech_instrument NULL budgetary

so here i dont want column which has null values,so my output is look like this:

tech_process tech_instrument budgetary
-----------------------------------------------
tech_process tech_instrument budgetary

so please modify my query and help me pleaeeeeeeeeeee

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-07 : 01:39:31

Don't quite understand your requirement. You do not want to show the tech_mech and commercial column ? Just excude the column name from the select


select a.tech_process, a.tech_mech, a.tech_instrument, a.commercial, a.budgetary 
from
(
	select 	case when tech_process 	  = 'sasi' and q_no='q5' then 'tech_process' 	end as 'tech_process',
		case when tech_mech 	  = 'sasi' and q_no='q5' then 'tech_mech' 	end as 'tech_mech',
		case when tech_instrument = 'sasi' and q_no='q5' then 'tech_instrument' end as 'tech_instrument',
		case when commercial 	  = 'sasi' and q_no='q5' then 'commercial' 	end as 'commercial',
		case when budgetary 	  = 'sasi' and q_no='q5' then 'budgetary' 	end as 'budgetary' 
	from 	quotation 
	where 	tech_process 	= 'sasi' 
	and 	tech_mech 	= 'sasi' 
	or 	tech_instrument = 'sasi'
	or 	commercial	= 'sasi' 
	or 	budgetary	= 'sasi'
) a 
where 	a.tech_process 	is not null 
or 	a.tech_mech 	is not null

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-10-07 : 02:15:18

hi khtan,
this is my query output:

tech_process tech_mech tech_instrument commercial budgetary
------------- ---------- ---------------- ----------- ----------
tech_process NULL tech_instrument NULL budgetary

actually tech_mech,commercial value is null ,so that only i said i dont want that columns,if there is any value in tech_mech,commercial then i need allthe thing. i think now u can clarify my doubt ,please help me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-07 : 03:36:13

This is absurd requirement!!

You should either add column as a part of the output or not, there is no conditional column addition. Resultset is always square, not jagged, you can't show column based on some condition.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-10-07 : 03:45:55

This is the same question you asked three days ago, you are going to get the same answers.

If you want to "disappear" columns that are NULL from your output you should do this in the front-end.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72975

Kristen

born2acheive
Yak Posting Veteran

65 Posts

Posted - 2006-10-07 : 03:57:27

thank you guya for sweet replys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources