The following SQL statement shows how to combine a column into a single text string – separated by a comma.
Would be combined as:
Fred, Jim, Jane, Betty
To use, replace Name with the name of your column and table with the name of your table.
(SELECT N', ' + Name FROM table FOR XML PATH(''),TYPE)
If you need to filter the values before combining you can add the WHERE statement after the table name, for example to only show names starting with a J:
(SELECT N', ' + Name FROM table WHERE Name like 'J%' FOR XML PATH(''),TYPE)
The following SQL can be used to remove leading and trailing spaces from a columns values.
Ideally the system that is feeding the data into the database should be doing this — but if it does not, or hasn’t in the past, this may be necessary to tidy up the records.
- replace table_name and table_column with your values
- always backup a database before performing bulk changes (or at very least export the table)
- this SQL uses WHERE conditions to reduce write operations — only values that need to be updated will be.
UPDATE table_name SET table_column = LTRIM(RTRIM(table_column))
WHERE table_column like '% ' OR table_column like ' %'
The following database privileges are the least required to allow WordPress to operate completely and reliably.
These permissions can be used to run a standard WordPress installation as well as a multisite installation without giving an unnecessary control to the database user.
The following steps show how to make a Crystal Report show only unique records.
This example is using Crystal Reports 2008 (version 12).
When the report is ran you can see that there are several rows (records) that are duplicated, in this example this is because the data-source provides other information that is unique to each row – for example ‘Andrew’ has three cars and ‘Anne’ has two.
Because we’re not showing that level of detail in the report we can use the ‘Database’ -> ‘Select Distinct Records’ option to filter the duplicate records (rows).
Each record (row) will now be unique, duplicate rows will not be displayed in the report or included in formulas, running totals etc.