Export (0) Print
Expand All

Generate

SQL Server 2000

Generate

Applies a set to each member of another set and joins the resulting sets by union. Alternatively, returns a concatenated string created by evaluating a string expression over a set.

Syntax
Set

Generate(«Set1», «Set2»[, ALL])

String

Generate(«Set», «String Expression»[, «Delimiter»])

Remarks

The set version of this function applies «Set2» to each member of «Set1» and joins the resulting sets by union. If ALL is specified, duplicates in the result are retained.

The string version of this function iterates through each member of the set specified in «Set», evaluates a string expression, specified in «String Expression», against the member and concatenates the result into the return string. Optionally, the string can be delimited by supplying a string expression in «Delimiter», separating each result in the concatenated return string.

Examples
Set
Generate({USA, France}, Descendants(Geography.CurrentMember, Cities))

For each member of the set {USA, France}, this function applies the expression Descendants(Geography.CurrentMember, Cities). Each such application results in a set. (Application to USA will generate the set of all cities in USA; application to France will generate all cities in France.) These sets are joined by union to return the result of this function. In this example, all cities in USA and France will be the result. In general, Generate(«Set1», «set_expression») will apply «set_expression» to each member of «Set1» and join the results by union.

If «Set1» is not related to «set_expression» by means of CurrentMember, then Generate results in a simple replication of the set implied by «set_expression», with as many replications as there are tuples in «Set1». If the optional ALL flag is specified, all duplicates are retained in the result. If ALL is not specified, duplicates are removed. For example,

Generate({USA, FRANCE}, {SEATTLE, BOSTON}, ALL)

returns the set

{SEATTLE, BOSTON, SEATTLE, BOSTON}.

However, if ALL was not specified, then the set returned is

{SEATTLE, BOSTON}.
String

The following example returns the string "19971998":

Generate( {Time.[1997], Time.[1998]}, Time.CurrentMember.Name)

The following example returns the string "1997 and 1998":

Generate( {Time.[1997], Time.[1998]}, Time.CurrentMember.Name, " and ")
Show:
© 2014 Microsoft