SELECT type as result FROM tbl_users
Result:
admin
admin
user
admin
editor
SELECT GROUP_CONCAT(type) as result FROM tbl_users
Result:
admin,admin,user,admin,editor
https://database.guide/how-to-return-query-results-as-a-comma-separated-list-in-mysql/
MSSQL equivalent is STRING_AGG()
SELECT type as result FROM tbl_users
Result:
admin
admin
user
admin
editor
SELECT STRING_AGG(type,',') as result FROM tbl_users
Result:
admin,admin,user,admin,editor
https://database.guide/the-sql-server-equivalent-to-group_concat/
MYSQL equivalent is GROUP_CONCAT()
<cfset str=reReplace(str, "[^\x20-\x7E]", "", "ALL")>
source: http://shemy-coldfusion.blogspot.com/2013/04/remove-non-ascii-characters-in.html
<cfset FinYears = arrayNew(1)> <cfset arrayAppend(FinYears ,2010)> <cfset arrayAppend(FinYears ,2011)> <cfset arrayAppend(FinYears ,2012)> <cfset arrayAppend(FinYears ,2013)> <cfset arrayAppend(FinYears ,2014)> <cfset arrayAppend(FinYears ,2015)> <cfset arrayAppend(FinYears ,2016)> <cfset arrayAppend(FinYears ,2017)> <cfset x = arraySlice(FinYears, 1, 3)> <cfset y = arraySlice(FinYears, 2, 3)> <cfset z = arraySlice(FinYears, -3, 3)>
x = 2010,2011,2012
y = 2011,2012,2013
z = 2015,2016,2017
SELECT tbl_a.*, tbl_b.* FROM tbl_a INNER JOIN tbl_b ON tbl_a.name = tbl_b.name OR ( tbl_a.name IS NULL AND tbl_b.name IS NULL )
<cffunction name="test" access="remote">
<cfset var Local = structNew()>
<cfset struktura = structNew()>
<cfset x = 1>
<cfset struktura['q_#x#'] = queryNew("name")>
<cfset queryAddRow(struktura['q_#x#'], 1)>
<cfset querySetCell(struktura['q_#x#'], "name", 'pero')>
<cfset queryAddRow(struktura['q_#x#'], 1)>
<cfset querySetCell(struktura['q_#x#'], "name", 'mirko')>
<cfset x = 2>
<cfset struktura['q_#x#'] = queryNew("name")>
<cfset queryAddRow(struktura['q_#x#'] , 1)>
<cfset querySetCell(struktura['q_#x#'], "name", 'slavko')>
<cfset queryAddRow(struktura['q_#x#'], 1)>
<cfset querySetCell(struktura['q_#x#'], "name", 'mirko')>
<cfquery name="q_data" dbtype="query">
<cfset count = 0>
<cfloop collection="#struktura#" item="key" >
<cfset count = count+1>
<cfif count gt 1 >
UNION all
</cfif>
SELECT * FROM struktura.#key#
</cfloop>
</cfquery>
<cfdump var="#q_data#">
</cffunction>
Create and call function
CREATE FUNCTION dbo.PK_DistinctList ( @List VARCHAR(MAX), @Delim CHAR ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @ParsedList TABLE ( Item VARCHAR(MAX) ) DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX) SET @list = LTRIM(RTRIM(@list)) + @Delim SET @pos = CHARINDEX(@delim, @list, 1) WHILE @pos > 0 BEGIN SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1))) IF @list1 <> '' INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX))) SET @list = SUBSTRING(@list, @pos+1, LEN(@list)) SET @pos = CHARINDEX(@delim, @list, 1) END SELECT @rlist = COALESCE(@rlist+',','') + item FROM (SELECT DISTINCT Item FROM @ParsedList) t RETURN @rlist END
and then call:
SELECT dbo.PK_DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') as DistinctList
OR
SELECT dbo.PK_DistinctList(tbl_persons.phone_numbers,',') as DistinctPhones
Source: https://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/
select nullif(name, '') from tbl_persons
Wrong:
SELECT CASE WHEN gender='1' THEN 'male' as gender_description, 'blue' as color_description ELSE 'female' as gender_description, 'red' as color_description END FROM tbl_persons
Right:
SELECT CASE WHEN gender='1' THEN 'male' ELSE 'female' END as gender_description, CASE WHEN gender='1' THEN 'blue' ELSE 'red' END as color_description FROM tbl_persons
UPDATE tbl_persons SET gender_description = CASE WHEN gender = '1' THEN 'male' WHEN gender = '2' THEN 'female' ELSE 'unknown' END