TryCF Gist

trycf-gist-1537652066748-89a4469d-8b33-4e06-57ce-21dbcbab4470.cfm

<cfscript>
    // Build a fake query object.
	HospitalVisits2 = queryNew(
		"patientID, Reason_For_Visit",
		"integer, varchar",
		[
		      { patientID: 1, Reason_For_Visit: "Broken leg" }
		    , { patientID: 1, Reason_For_Visit: "Concussion" }
		    , { patientID: 1, Reason_For_Visit: "Severe bleeding" }
		    , { patientID: 1, Reason_For_Visit: "BROKEN LEG" } // Duped value in different case.
		    , { patientID: 1, Reason_For_Visit: "Aches,pain" } // Comma in value
		    , { patientID: 1, Reason_For_Visit: "" } // Empty value
    	    
		]
	);
	writeDump(HospitalVisits2); // What's in our query?
	
	newReasonList = listChangeDelims( listRemoveDuplicates( valuelist(HospitalVisits2.Reason_For_Visit, "|"),"|",true ),", ","|",false ) ;

</cfscript>

<br><br>

This is our main query: <br>
<cfoutput>#newReasonList#</cfoutput>

<br><br><br><br>

What if we have an empty query? 
<br>

<cfscript>
	// Build a fake empty query.
	HospitalVisits_Empty = queryNew(
		"patientID, Reason_For_Visit",
		"integer, varchar"
	);
	writeDump(HospitalVisits_Empty); // What's in our query?

	
	emptyQueryReasonList = listChangeDelims( listRemoveDuplicates( valuelist(HospitalVisits_Empty.Reason_For_Visit, "|"),"|",true ),", ","|",false ) ;
</cfscript>

This is our empty de-duped list: <cfoutput>#emptyQueryReasonList#</cfoutput>



<br><br><br><br>

What if we have only one row in our query? 
<br>

<cfscript>
	// Build a fake empty query.
	HospitalVisits_JustOne = queryNew(
		"patientID, Reason_For_Visit",
		"integer, varchar",
		[
		      { patientID: 1, Reason_For_Visit: "Broken leg" }

		]
	);
	writeDump(HospitalVisits_JustOne); // What's in our query?

	
	justoneQueryReasonList = listChangeDelims( listRemoveDuplicates( valuelist(HospitalVisits_JustOne.Reason_For_Visit, "|"),"|",true ),", ","|",false ) ;
</cfscript>

This is our single value list: <cfoutput>#justoneQueryReasonList#</cfoutput>


<br><br><br><br>

What if we have only one row in our query with an empty element? 
<br>

<cfscript>
	// Build a fake empty query.
	HospitalVisits_JustOneEmpty = queryNew(
		"patientID, Reason_For_Visit",
		"integer, varchar",
		[
		      { patientID: 1, Reason_For_Visit: "" }

		]
	);
	writeDump(HospitalVisits_JustOneEmpty); // What's in our query?

	
	justoneemptyQueryReasonList = listChangeDelims( listRemoveDuplicates( valuelist(HospitalVisits_JustOneEmpty.Reason_For_Visit, "|"),"|",true ),", ","|",false ) ;
</cfscript>

This is our single value list: <cfoutput>#justoneemptyQueryReasonList#</cfoutput>