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

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

Re: Combine Multiple Rows into one (denormalize) explain the SQL

Show all headers | View raw

bill (billmaclean1@gmail.com) writes:
> 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.

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:

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

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@sommarskog.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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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