Create function concatenate
in [Programming SQL]
|
From: --CELKO-- on 14 Jun 2010 12:25 >> I hope that the full story helps you understanding my need. << This is a very common newbie design error. The give-away was when you did not know that fields and columns are totally different. You want to format data in the database, thus destroying First Normal Form (1NF) and a tiered architecture. The right way to do this is to use a report writer. There is a good chance that your client has a copy of reporting services, owns a copy Crystal Reports (or other commercial packages) or can get some Open Source tool. Do not saw wood with a hammer. When I did queries like this, we had to prioritize the categories. It was in a Prison system, so it was more important to see that an inmate was a serial murderer than to see his shoplifting convictions. This is easy to do in a query since the Criminal Code is a hierarchy -- like Dewey Decimal number for evil :) I hope that you also took the time to design the encoding schemes for the client's categories.
From: Erland Sommarskog on 14 Jun 2010 17:55
Steph (smarcoux(a)cbgi.qc.ca) writes: > almost every view contains a table containing multiple values for the same > record... ex: categories... a client can be linked to an indefinite number > of categories. > When we look at the report, we don't want to see 5 rows for the same > client if it is linked to 5 categories. One row max. But we want to see > the 5 categories in one field. > > That's why I createde a stored procedure which concatenates a field or > expression from a view/table and it returns one varchar(4000) field. I > use some parameters (tbl_name, fld_name, where_clause, order_by_clause). > I f for one view I need to see the category code, I use "Code" for > fld_name and if I need to see the description, I use "Categ_Desc". The > stored procedure is working fine when I use exec but I want to use it in > a view like I would do with a function. I know that I could create > subqueries each time to concatenate the data but I wanted to lose less > time doing something that 's already done several times. I don't want to > have 200 functions for every concatenation possibilities they ask me. If you want some generic function, you could write a user-defined aggregegate using the CLR. But beware that there is no way to have the lists be ordered if you go this route. Else, you will have to write subqueries using FOR XML PATH, as descrubed on 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 |