10 июня 2012 г. 14:43
DECLARE @table table (
SELECT * FROM @table
I want it to return in one string in the following format, with ";" seperating each email id:
10 июня 2012 г. 14:50
SELECT STUFF( (SELECT DISTINCT '; ' + EmailName
FROM @table I FOR XML PATH('')
), 1, 2, '');
Many Thanks & Best Regards, Hua Min
- Помечено в качестве ответа Milli_22 10 июня 2012 г. 15:22
11 июня 2012 г. 2:32Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Those proprietary table variables area sign of bad SQL. It is a way to fake a deck of punch cards or a fake magnetic scratch tape. T-SQL has had row constructors for years. See how we do it in one statement without forcing disk access.
FROM (VALUES ('email@example.com'),
('firstname.lastname@example.org'))AS X (email_address);
>> I want it to return in one string in the following format, with ";" separating each email id:<<
Why do you wish to destroy First Normal Form (1NF) with a concatenated list structure? Normal forms are the foundation of RDBMS, after all. Your approach to SQL is completely wrong; please read just one book before you try to code again
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.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL