3
Answers

case create a problem in sql server

hi every buddy,

plz solve my problem if any buddy understand. when i use case it throw error


set @sql ='select *
from
(
select  tsim.OrganisationCode orgcode , (case when tsim.OrganisationName is not null then '+'yes'+' else null end) as orgname,p.RouteofAdministration  from tbl_Product p
inner join tbl_ProductMaster as pm on p.fk_ProductMasterId=pm.ProductMasterId
inner join tbl_ProductSiteInpectedBy as ps on ps.fk_ProductMasterID=pm.ProductMasterId
inner join tbl_SiteInspectedBy_Master as tsim on tsim.OrganisationID=ps.fk_OrganisationID
) d
pivot
(
max(orgcode)  for orgname in ('+@columns+')
) as t2'


exec(@sql)

Answers (3)
0
sudipta sanyal

sudipta sanyal

NA 934 3.8k 10y
set @sql ='select *
from
(
select  tsim.OrganisationCode orgcode , (case when tsim.OrganisationName is not null then ''yes'' else null end) as orgname,p.RouteofAdministration  from tbl_Product p
inner join tbl_ProductMaster as pm on p.fk_ProductMasterId=pm.ProductMasterId 
inner join tbl_ProductSiteInpectedBy as ps on ps.fk_ProductMasterID=pm.ProductMasterId
inner join tbl_SiteInspectedBy_Master as tsim on tsim.OrganisationID=ps.fk_OrganisationID
) d pivot
(
max(orgcode)  for orgname in ('+@columns+')
) as t2'


exec(@sql)
0
Joginder Banger

Joginder Banger

NA 10k 490.3k 10y
not done ....same problem again
0
Jaimin Soni

Jaimin Soni

NA 21 0 10y
try this set @sql ='select * from ( select tsim.OrganisationCode orgcode , (case when tsim.OrganisationName is not null then yes else null end) as orgname,p.RouteofAdministration from tbl_Product p inner join tbl_ProductMaster as pm on p.fk_ProductMasterId=pm.ProductMasterId inner join tbl_ProductSiteInpectedBy as ps on ps.fk_ProductMasterID=pm.ProductMasterId inner join tbl_SiteInspectedBy_Master as tsim on tsim.OrganisationID=ps.fk_OrganisationID ) d pivot ( max(orgcode) for orgname in ('+@columns+') ) as t2' exec(@sql)