Create function concatenate
in [Programming SQL]
|
From: Steph on 7 Jun 2010 11:43 I want to know How I can create a function that will concatenate fields ex: UF_Concat_fields (Tbl_Name, Field_Name, Where_Clause, Separ_Char) In that function, I could do select Field_Name from tbl_Name where Where_Clause in a cursor or something like that and add the Separ_Char which would = ", ". ex: UF_Concat_fields ("tbl_colors", "Desc", "chk_active = 1", ', ') the Result would be "Red, Blue, Green, Yellow" ex: UF_Concat_fields ("tbl_numbers", "Desc", "id1 < 20", ' - ') the Result would be "One - two - three - ten" Thanks Steph
From: Plamen Ratchev on 7 Jun 2010 12:34 To accomplish this you would need dynamic SQL and it cannot be used in functions. However, you can create a stored procedure. A couple references: Dynamic SQL: http://www.sommarskog.se/dynamic_sql.html Dynamic search conditions: http://www.sommarskog.se/dyn-search.html Concatenation (look for the XML PATH method): http://www.projectdmx.com/tsql/rowconcatenate.aspx -- Plamen Ratchev http://www.SQLStudio.com
From: Steph on 11 Jun 2010 09:40 I created the dynamic stored procedure but it is useless... I can't use it in my select statement like I can do in a function... My stored procedure always returns a varchar(4000) field containing the values concatenated. Exec SP_Concat_Fields('MyTbl', 'MyFld', 'Where Code = ''A''', 'Order By ID') This returns the following: "One, Two, Three, Four, Five" It is not possible that Microsoft never thought about using a stored procedure in a select statement... I found the Openrowset function but it seems dangerous for security reason to activate the "Ad Hoc Distributed Queries" Any other way which is safe? Steph "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:on7q06hsqgklt0401acc57j51novsophmr(a)4ax.com... > To accomplish this you would need dynamic SQL and it cannot be used in > functions. However, you can create a stored procedure. > > A couple references: > Dynamic SQL: http://www.sommarskog.se/dynamic_sql.html > Dynamic search conditions: http://www.sommarskog.se/dyn-search.html > Concatenation (look for the XML PATH method): > http://www.projectdmx.com/tsql/rowconcatenate.aspx > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Erland Sommarskog on 11 Jun 2010 18:22 Steph (smarcoux(a)cbgi.qc.ca) writes: > I created the dynamic stored procedure but it is useless... I can't use it > in my select statement like I can do in a function... > My stored procedure always returns a varchar(4000) field containing the > values concatenated. > > Exec SP_Concat_Fields('MyTbl', 'MyFld', 'Where Code = ''A''', 'Order By > ID') This returns the following: "One, Two, Three, Four, Five" > > It is not possible that Microsoft never thought about using a stored > procedure in a select statement... Permitting that is non-trivial, since a stored procedure can return a result set in different shape each time. Rather trying to answer your question, I would suggest that you tell us the full story what you are trying to achieve, because I get a feeling that you are off-track entirely. -- 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: Steph on 14 Jun 2010 10:00
The full story... I have a client who wants a lot of reports and a lot of views. 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. I hope that the full story helps you understanding my need. thanks Steph "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D953E467F1BYazorman(a)127.0.0.1... > Steph (smarcoux(a)cbgi.qc.ca) writes: >> I created the dynamic stored procedure but it is useless... I can't use >> it >> in my select statement like I can do in a function... >> My stored procedure always returns a varchar(4000) field containing the >> values concatenated. >> >> Exec SP_Concat_Fields('MyTbl', 'MyFld', 'Where Code = ''A''', 'Order By >> ID') This returns the following: "One, Two, Three, Four, Five" >> >> It is not possible that Microsoft never thought about using a stored >> procedure in a select statement... > > > Permitting that is non-trivial, since a stored procedure can return > a result set in different shape each time. > > Rather trying to answer your question, I would suggest that you tell > us the full story what you are trying to achieve, because I get a > feeling that you are off-track entirely. > > > -- > 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 > |