Is any group function which concatenates data?
in [Programming SQL]
|
From: PrzemekD on 15 Jun 2010 19:29 Hello. In a query as follows I would like to concatenate data instead of aggregating in a field which is not a part of group clause. SELECT invoice_no, device_type, concatenate_text(device_serial),customer FROM t1,t2,t3,t4 WHERE .... group by invoice_no, device_type, customer Full data set without grouping looks like: SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA SRW/2/2010, AAASSSQQWE, sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT I would like to have something like this after group by and "concatenate": SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX/sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT -- Przemek http://www.e-wolomin.pl
From: Erland Sommarskog on 17 Jun 2010 07:09 PrzemekD (biuro@_e-wolo_min.pl_) writes: > In a query as follows I would like to concatenate data instead of > aggregating in a field which is not a part of group clause. > > SELECT invoice_no, device_type, concatenate_text(device_serial),customer > FROM t1,t2,t3,t4 > WHERE .... > group by invoice_no, device_type, customer > > Full data set without grouping looks like: > SRW/1/2010, AAASSSQQWE, sn000001, ABIX > SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA > SRW/2/2010, AAASSSQQWE, sn000002, ADA > SRW/3/2010, AAASSSQQWE, sn000001, ADA > SRW/4/2010, AAASSSQQWE, sn000001, ADT > > I would like to have something like this after group by and "concatenate": > > SRW/1/2010, AAASSSQQWE, sn000001, ABIX > SRW/2/2010, AAASSSQQWE, MSD001XXX/sn000002, ADA > SRW/3/2010, AAASSSQQWE, sn000001, ADA > SRW/4/2010, AAASSSQQWE, sn000001, ADT There is no such function built-in, but you could write a user-defined aggregate. Better however, is to use FOR XML PATH, see http://www.projectdmx.com/tsql/rowconcatenate.aspx, -- Erland Sommarskog, SQL Server MVP, esquel(a)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 SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: --CELKO-- on 17 Jun 2010 17:01 Why do you wish to destroy First Normal Form (1NF) with a concatenated list structure? Normal forms are the foundation of RDBMS, after all. Why are you formatting data in the back end? The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This is a more basic programming principle than just SQL and RDBMS. Get a reporting tool so you can do a sort for your lists and lay them out on paper or a screen in a way that a human being can read them.
From: Tony Rogerson on 18 Jun 2010 16:59 > Why do you wish to destroy First Normal Form (1NF) with a concatenated > list structure? Normal forms are the foundation of RDBMS, after > all. Yes - 1NF is the entry bar, but where on earth is this being broken? The results required are... "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:bd9f2698-b8d1-4dde-878f-4f7977e0e9ec(a)h13g2000yqm.googlegroups.com... > Why do you wish to destroy First Normal Form (1NF) with a concatenated > list structure? Normal forms are the foundation of RDBMS, after > all. > > Why are you formatting data in the back end? The basic principle of a > tiered architecture is that display is done in the front end and never > in the back end. This is a more basic programming principle than just > SQL and RDBMS. > > Get a reporting tool so you can do a sort for your lists and lay them > out on paper or a screen in a way that a human being can read them.
From: Tony Rogerson on 18 Jun 2010 17:02
Yes - 1NF is the entry bar, but where on earth is this being broken? The results required are... SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX/sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT The key looks to be the first attribute but we don't have enough information, RVA's are fine in the relational model so that means MSD001XXX/sn000002, ADA is fine so long as its an atomic set - note SQL is NOT relational theory, SQL completely bastardises it. Seriously, where did you learn your trade? --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:bd9f2698-b8d1-4dde-878f-4f7977e0e9ec(a)h13g2000yqm.googlegroups.com... > Why do you wish to destroy First Normal Form (1NF) with a concatenated > list structure? Normal forms are the foundation of RDBMS, after > all. > > Why are you formatting data in the back end? The basic principle of a > tiered architecture is that display is done in the front end and never > in the back end. This is a more basic programming principle than just > SQL and RDBMS. > > Get a reporting tool so you can do a sort for your lists and lay them > out on paper or a screen in a way that a human being can read them. |