Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1120

Combine Multiple Rows into one (denormalize) explain the SQL

Show all headers | View raw

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)

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Combine Multiple Rows into one (denormalize) explain the SQL bill <billmaclean1@gmail.com> - 2012-06-13 15:48 -0700
  Re: Combine Multiple Rows into one (denormalize) explain the SQL "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-06-14 10:57 -0400
  Re: Combine Multiple Rows into one (denormalize) explain the SQL Erland Sommarskog <esquel@sommarskog.se> - 2012-06-14 21:49 +0200
    Re: Combine Multiple Rows into one (denormalize) explain the SQL bill <billmaclean1@gmail.com> - 2012-06-18 12:33 -0700

csiph-web