SQLTeam.com | Weblogs | Forums

Case statement to modify


#1

I have the following sql here and the result I am getting is also given,

Is there a way that I can get these in one row,


,CASE WHEN Field.DataItemCode='Dosagelow' THEN 'Yes' ELSE '' END [Dosage Low]
,CASE WHEN Field.DataItemCode='Uom' THEN 'Yes' ELSE '' END [Uom]
,CASE WHEN Field.DataItemCode='OrderRouteCode' THEN 'Yes' ELSE '' END [OrderRouteCode]
,CASE WHEN Field.DataItemCode='FrequencyCode' THEN 'Yes' ELSE '' END [FrequencyCode]


#2

Change your query to use a suitable aggregate function (e.g. MAX) and then include the other columns in a group by clause as shwon below

,MAX(CASE WHEN Field.DataItemCode='Dosagelow' THEN 'Yes' ELSE '' END )[Dosage Low] 
,MAX(CASE WHEN Field.DataItemCode='Uom' THEN 'Yes' ELSE '' END ) [Uom] 
,MAX(CASE WHEN Field.DataItemCode='OrderRouteCode' THEN 'Yes' ELSE '' END ) [OrderRouteCode] 
,MAX(CASE WHEN Field.DataItemCode='FrequencyCode' THEN 'Yes' ELSE '' END ) [FrequencyCode]
FROM
	.....
WHERE
	.....
GROUP BY
	OrderEntryFormName, Title, DESCRIPTION

#3

Great .. . That works . . Thank you so much :slight_smile: