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?

1
  • Irrelevant but the where looks to complicated. It can surely be simplified. Jun 24 '16 at 15:58
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
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? Jun 24 '16 at 15:28
  • What UI are you using? SMSS?
    – Philᵀᴹ
    Jun 24 '16 at 15:30
  • yes, version 12.0 Jun 24 '16 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. Jun 24 '16 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
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)

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 Jun 27 '16 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
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 May 13 '20 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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