2 
declare @bid varchar(4) = '1007'    

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    sr.route_description

from sri_schedule_releases_view as sr

where 
    ((case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or 
    (case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))

order by sr.item_id asc

This query works fine. But I want to display sr.route_description only who has the sr.route_code PM 01, PM 03 and PM 05.

Are there any possibilities to do this?

Improve this question
1
  • Irrelevant but the where looks to complicated. It can surely be simplified.
    – ypercubeᵀᴹ
    Jun 24, 2016 at 15:58

4 Answers 4

Reset to default
2

You just need to add a case statement to your select list:

declare @bid varchar(4) = '1007'    

select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released,
    sr.extended_sell,
    sr.enough,
    case when sr.route_code in ('pm 01','pm 03','pm 05') then sr.route_description else '' end as route_description
from sri_schedule_releases_view as sr
where 
    ((case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
    end = @bid) or 
    (case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
    end = @bid) or
    (case 
        when sr.route_code in ('pm 01','pm 03','pm 05') then (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')
    end = @bid) or
    (sr.contact_id = @bid))
order by sr.item_id asc
Improve this answer
4
  • I tried the same thing but if there are no data in the column, still column heading visible. is there any chance to get rid of the column heading?
    – Marin Mohanadas
    Jun 24, 2016 at 15:28
  • What UI are you using? SMSS?
    – Philᵀᴹ
    Jun 24, 2016 at 15:30
  • yes, version 12.0
    – Marin Mohanadas
    Jun 24, 2016 at 15:31
  • 3
    SSMS can't "hide" columns conditionally. You either include the column in your output, and SSMS displays it, or you don't include it in the output. You would need dynamic SQL or conditional logic to achieve the latter.
    – Aaron Bertrand
    Jun 24, 2016 at 16:33
1

I found a solution and it works as I expected

declare @bid varchar(4) = '1007'    

declare @pm1 varchar(5) = (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'John')
declare @pm3 varchar(5) = (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Jane')
declare @pm5 varchar(5) = (select users.contact_id from users where users.delete_flag = 'n' and users.name = 'Doe')

if @bid in (@pm1,@pm3,@pm5) 
begin
select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released as available_qty,
    sr.extended_sell as ext_sell,
    sr.enough,
    sr.route_description

from sri_schedule_releases_view as sr

where 
   ((case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm1
   end = @bid) or 
   (case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm3
   end =@bid) or
   (case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm5
   end = @bid) or
   (sr.contact_id = @bid))
end
else
begin
select sr.release_date,
    sr.customer_id,
    sr.customer_name,
    sr.item_id,
    sr.item_desc,
    sr.order_no,
    sr.qty_on_hand,
    sr.release_qty,
    sr.qty_after_released as available_qty,
    sr.extended_sell as ext_sell,
    sr.enough

from sri_schedule_releases_view as sr

where 
   ((case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm1
   end = @bid) or 
   (case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm3
   end =@bid) or
   (case 
      when sr.route_code in ('pm 01','pm 03','pm 05') then @pm5
   end = @bid) or
   (sr.contact_id = @bid))
end
Improve this answer
0

If you want to only show the column if data is present then maybe create a function with two possibilities inside IF statements:

IF data is present (Run the normal query) ELSE
(copy the same query but without the empty column)

Improve this answer
1
  • if I run a query within if statement in a function that query became a sub query. So I cannot retrieve each columns at once
    – Marin Mohanadas
    Jun 27, 2016 at 14:51
0

Too much code--just create a temp table with the entire result-set.

SELECT * INTO #tmpTbl FROM MyDatabase.dbo.tbl 

ALTER TABLE 

REMOVE columname

SELECT * FROM #tmpTbl 

DROP TABLE #tmpTbl
Improve this answer
1
  • there might be issues with this approach. if it is an one off and a small result set in a not so busy server - maybe - otherwise no
    – Marcello Miorelli
    May 13, 2020 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.