I hate denormalization, so no need to tell me about the difference
between front ands and back ends, the need for a proper data model
etc.

I found this SQL (scroll to the bottom) that will "flatten" data from
multiple rows into a single multi valued column. I didn't write the
SQL but can't remember where on the web I found it.

In any case, can someone point me to a step by step explanation of how
this SQL works, or perhaps provide the explanation here?

I have definitely not seen the part at the end where the sub-query is
aliased as g, and then a parenthesized letter (y) is put at the end

It appears that g.y becomes a column, but the syntax is confusing to
me.

Thanks,

Bill

DECLARE @Sample TABLE (ID INT, SubID INT)

INSERT @Sample
SELECT 1234, 112 UNION ALL
SELECT 111, 4444 UNION ALL
SELECT 1234, 113 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 1234, 114 UNION ALL
SELECT 3234, 212 UNION ALL
SELECT 3234, 213 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 1110, 12 UNION ALL
SELECT 111, 213 UNION ALL
SELECT 3234, 214

SELECT i.ID,
STUFF(g.y, 1, 1, '') AS SubIDs
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS i
CROSS APPLY (
SELECT DISTINCT ',' + CAST(SubID AS VARCHAR(11))
FROM @Sample AS s
WHERE s.ID = i.ID
ORDER BY ',' + CAST(SubID AS VARCHAR(11))
FOR XML PATH('')
) AS g (y)

bill wrote:
<snip>
A derived table is aliased with g, and the name of the column in the derived
table is aliased with y. Notice that the column name was not provided in the
derived table select statement. Even if it was, it would be overridden by
the alias.

Similar syntax can be seen with the CREATE VIEW statement:
CREATE VIEW ViewName (colname1,...,colnameN) AS
SELECT ...

bill (billmaclean1 (AT) gmail (DOT) com) writes:
Rather than writing

(SELECT somecol, cnt = COUNT(*)
FROM tbl
GROUP BY somecol) AS T

You can write

(SELECT somecol, COUNT(*)
FROM tbl
GROUP BY somecol) AS T (somecol, cnt)

You don't see this very often, but sometimes there is no choice and
this is such a case:

FOR XML means that you want the result as an XML document. FOR XML
PATH is the best method to use when you want to customise the XML
document. The argument to PATH is the root tag, but as you see from
the example, you can leave it out. And if you have a single column
with no name you get a concatenated string. Add a column alias in
the subquery and see what happens!



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks to both Erland and Bob for your replies, that was helpful info.

Bill