In other words, the rows in a resultset of a query do not have a prescribed position, unless it is explicitly specified in the query expression.On the other hand, a concatenated list is an ordered structure. In fact, concatenation itself is an order-utilizing operation in the sense that values can be prefixed or post fixed to an existing list.So approaches that are loosely called “concatenating row values”, “aggregate concatenation” etc.

However, you can do these using different approaches in Transact SQL, but it is best to avoid such methods in long-term solutions Even though SQL, in general, deviates considerably from the relational model, its reliance on certain core aspects of relational foundations makes SQL functional and powerful.

One such core aspect is the set based nature of SQL expressions (well, multi-sets to be exact, but for the given context let us ignore the issue of duplication).

The primary idea is that tables are unordered and therefore the resultsets of any query that does not have an explicit ORDER BY clause is unordered as well.

Many a time, SQL programmers are faced with a requirement to generate report-like resultsets directly from a Transact SQL query.

In most cases, the requirement arises from the fact that there neither sufficient tools nor in-house expertise to develop tools that can extract the data as a resultset, and then massage the data in the desired display format.

Quite often folks are confused about the potential of breaking relational fundamentals such as the First Normal Form or the scalar nature of typed values.(Talking about 1NF violations in a language like SQL which lacks sufficient domain support, allows NULLs and supports duplicates is somewhat ironic to begin with, but that is a topic which requires detailed explanations.) By ‘Concatenating row values’ we mean this: You have a table, view or result that looks like this……and you wish to have a resultset like the one below: In this example we are accessing the sample North Wind database and using the following SQL The objective is to return a resultset with two columns, one with the Category Identifier, and the other with a concatenated list of all the Product Names separated by a delimiting character: such as a comma.It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate.How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column?A simple, and intuitive way of displaying data is surprisingly difficult to achieve.Anith Sen gives a summary of different ways, and offers words of caution over the one you choose.