SQL – How to concatenate/combine column values into comma separated text string

The following SQL statement shows how to combine a column into a single text string – separated by a comma.

For example:

Name
Fred
Jim
Jane
Betty

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 Stuff(
  (SELECT N', ' + Name FROM table FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

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 Stuff(
  (SELECT N', ' + Name FROM table WHERE Name like 'J%' FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

 

Reference: http://stackoverflow.com/a/3672860/1318464

SQL – How to remove spaces before and after database values

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.

Notes:

  • 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 ' %'

WordPress – What is the least database user privileges required?

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.

  • Alter
  • Create
  • Delete
  • Index
  • Select
  • Drop
  • Insert
  • Update

WordPress-LeastUserPerm1

Crystal Reports – Display unique records only

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.

CrystalReports-UnqiueValues1

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).

CrystalReports-UnqiueValues2

Each record (row) will now be unique, duplicate rows will not be displayed in the report or included in formulas, running totals etc.

CrystalReports-UnqiueValues3