Creating Delimited Strings
-
Of course, I see Jeff's "reverse split string function" when I don't need it. And when I do need it, I can't find the silly thing.
So.. @sigh. Trying to create a delimited string for email purposes. We have a table that's going to be creating errors when any one of 15 fields is NULL or blank. Instead of recording the first error, I want to record all errors so that the users fix them all at once instead of going back and forth finding new errors each time the job is run.
CREATE TABLE dbo.#TempErrors (DocID int, FieldType varchar(20), ErrorDetail varchar(400));
GO
INSERT INTO dbo.#TempErrors (DocID, FieldType)
SELECT DocID, 'DocumentNumber'
FROM MyStagingTable
WHERE 'DocumentNumber'IS NULL OR 'DocumentNumber'='';
/* repeat ad naseum for several fields -- this is the "real" code*/
INSERT INTO dbo.#TempErrors (DocID, FieldType)
SELECT 1, 'DocumentNumber'
UNION ALL
SELECT 1, 'Sale Date'
UNION ALL
SELECT 2, 'First Name'
UNION ALL
SELECT 3, 'Last Name'
UNION ALL
SELECT 3, 'DocumentNumber'
UNION ALL
SELECT 8, 'City'
UNION ALL
SELECT 10, 'Sale Date';
Expected output is (don't worry about the extraneous punctuation. I can alter code to take care of that):
DocID ErrorDetail
1 DocumentNumber;Sale Date
2 First Name
3 Last Name;DocumentNumber
8 City
10 Sale Date
Now. Can someone please point me to all those wonderful articles (which are all hiding from me, I know it, else my Google-Fu would have found them) that can help me with this?
I don't want XML results because I have to use this to update the ErrorDetail field on my table. I tried COALESCE, but I can't figure out how to use that without adding a loop / cursor around it and I'm really trying to avoid the loop / cursor route if I can.
-
I've used the FOR XML trick to get the data...it returns string data(varchar(800) or max
this seems to do what you asked for:
/*DocIDSkills
1DocumentNumber,Sale Date
2First Name
3Last Name,DocumentNumber
8City
10Sale Date
*/
SELECT DocID,stuff(( SELECT ',' + FieldType
FROM #TempErrors s2
WHERE s2.DocID= s1.DocID --- must match GROUP BY below
ORDER BY DocID
FOR XML PATH('')
),1,1,'') as [Skills]
FROM #TempErrors s1
GROUP BY s1.DocID --- without GROUP BY multiple rows are returned
ORDER BY s1.DocID
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
MySQL has the group_concat function which is one of the only places I have seen it outshine SQL Server, and I use the word outshine lightly. The lack of a built-in group_concat function in SQL Server can be easily remedied by implementing the CLR aggregate in this article:
http://www.projectdmx.com/tsql/rowconcatenate.aspx
Reproduced here as group_concat instead of strconcat in case that article moves:
Using Common Language Runtime
Though this article is about approaches using Transact SQL, this section is included due to the popularity of CLR aggregates in SQL 2005. Not only it empowers the CLR programmer with new options for database development, in some cases, they work at least as well as native Transact SQL approaches.
If you are familiar with .NET languages, SQL 2005 offers a convenient way to create user defined aggregate functions using C#, VB.NET or similar languages that is supported by the Common Language Runtime (CLR). Here is an example of a string concatenate aggregate function written using C#.
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public struct group_concat : IBinarySerialize{
private List values;
public void Init() {
this.values = new List();
}
public void Accumulate(SqlString value) {
this.values.Add(value.Value);
}
public void Merge(group_concat value) {
this.values.AddRange(value.values.ToArray());
}
public SqlString Terminate() {
return new SqlString(string.Join(", ", this.values.ToArray()));
}
public void Read(BinaryReader r) {
int itemCount = r.ReadInt32();
this.values = new List(itemCount);
for (int i = 0; i <= itemCount - 1; i++) {
this.values.Add(r.ReadString());
}
}
public void Write(BinaryWriter w) {
w.Write(this.values.Count);
foreach (string s in this.values) {
w.Write(s);
}
}
}
Once you build and deploy this assembly on the server, you should be able to execute your concatenation query as:
SELECT CategoryId,dbo.group_concat(ProductName)
FROM Products
GROUP BY CategoryId ;
If you are a total newbie on CLR languages, and would like to learn more about developing database solutions using CLR languages, consider starting at Introduction to Common Language Runtime (CLR) Integration
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.
Or maybe it was the guy at Simple Talk who needed permissions:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/%5B/url%5D
Lowell: Thanks for the XML, but I need to avoid XML (as stated in my first post) because I'm updating a table after I concat the results.
EDIT: I just found out from the Simple Talk editor that the other link (the one you provided) is Anith's personal site, so this isn't a case of copyright violation. That's good to know.
-
Stupid question. Does COALESCE *have* to use a variable in order to work correctly?
I'm trying a basic SELECT statement where I've replaced the variable with the column name. It's not working, of course.
SELECT DocumentID, COALESCE(FieldType + '; ', '') + FieldType
FROM dbo.#TempErrors;
EDIT: I forgot to change databases when I first tried it. Guess what? IT WORKS. But it gives me a record for each record in the temp table.
Here are my results:
DocumentID(No column name)
11494SaleDate; ShipDate
11494ShipDate; SaleDate
It flips the results in the second column. Now I just need to figure out how to ditch the duplicated record.
-
Brandie glad you found what you are after, and thanks for the link; I added some of those methods to my snippets.
If you get a chance, can you explain why you couldn't update from a sub select of that FOR XML method? I know i can do a simple update vie a CTE or sub select and UPDATE FROM... syntax, but I'm curious what blocks it's use in your case.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (4/7/2011)
If you get a chance, can you explain why you couldn't update from a sub select of that FOR XML method? I know i can do a simple update vie a CTE or sub select and UPDATE FROM... syntax, but I'm curious what blocks it's use in your case.My serious lack of XML knowledge. I know a bit of XML, but I'm under a time crunch for this project and I don't have time to bone up on my XML knowledge. I hate when circumstances do that to me (it's been happening to me all week), but it's getting to the point where I may have to give in and loop this sucker just because I don't have time to figure everything out.
-
I think the article of mine that you're looking for is here...
http://www.sqlservercentral.com/articles/Test+Data/61572/
HOWEVER, I believe that the very nice canned solution that Lowell provided is the better way and is VERY easy to use to update a table from.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh, duh. You're right and Lowell's right, of course. And that works so much better then the other stuff I've come up with.
Sorry, guys. Don't mean to be difficult. I'm just having a bad week.
-
Brandie Tarvin (4/7/2011)
Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.Or maybe it was the guy at Simple Talk who needed permissions:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/%5B/url%5D
Wow, they are identical!
No takers on the CLR function huh? C'mon, all the cool kids are trying it :Whistling:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (4/7/2011)
Brandie Tarvin (4/7/2011)
Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.Or maybe it was the guy at Simple Talk who needed permissions:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/%5B/url%5D
Wow, they are identical!
No takers on the CLR function huh? C'mon, all the cool kids are trying it :Whistling:
How do you control the concatenation order of the strings using this CLR function?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Okay, so repeating what I have...
CREATE TABLE dbo.#TempErrors (DocID int, FieldType varchar(20), ErrorDetail varchar(400));
GO
INSERT INTO dbo.#TempErrors (DocID, FieldType)
SELECT DocID, 'DocumentNumber'
FROM MyStagingTable
WHERE 'DocumentNumber'IS NULL OR 'DocumentNumber'='';
/* repeat ad naseum for several fields -- this is the "real" code*/
INSERT INTO dbo.#TempErrors (DocID, FieldType)
SELECT 1, 'DocumentNumber'
UNION ALL
SELECT 1, 'Sale Date'
UNION ALL
SELECT 2, 'First Name'
UNION ALL
SELECT 3, 'Last Name'
UNION ALL
SELECT 3, 'DocumentNumber'
UNION ALL
SELECT 8, 'City'
UNION ALL
SELECT 10, 'Sale Date';
Expected output is (don't worry about the extraneous punctuation. I can alter code to take care of that):
DocID ErrorDetail
1 DocumentNumber;Sale Date
2 First Name
3 Last Name;DocumentNumber
8 City
10 Sale Date
Using Lowell's recommended SELECT, final UPDATE turns out to be:
UPDATE samf
SET ErrorFlag = 1, ErrorDetail = samf2.ErrorDetail
FROM DT.Staging samf
INNER JOIN (SELECT DocID,stuff(( SELECT ',' + FieldType
FROM dbo.#TempErrors s2
WHERE s2.DocID= s1.DocID --- must match GROUP BY below
ORDER BY DocID
FOR XML PATH('')
),1,1,'') as [ErrorDetail]
FROM dbo.#TempErrors s1
GROUP BY s1.DocID /* without GROUP BY multiple rows are returned */ ) samf2
ON samf.DocID = samf2.DocID
Initially, I made the mistake of trying to change the temp table names to my Staging table name, which is what screwed me up and made me think it was going to take to much time to "fix" the code. Silly me.
-
opc.three (4/7/2011)
Brandie Tarvin (4/7/2011)
Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.Or maybe it was the guy at Simple Talk who needed permissions:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/%5B/url%5D
Wow, they are identical!
No takers on the CLR function huh? C'mon, all the cool kids are trying it :Whistling:
I need to learn CLR. But I have cluster failures, emergency projects, and all sorts of other fires happening in the office which prevent me from taking the time I need to learn this stuff properly. I hate being in fire-fighting mode. It makes me sloppy.
-
Mark-101232 (4/7/2011)
opc.three (4/7/2011)
Brandie Tarvin (4/7/2011)
Wow. I wonder if they had permissions to reprint that article. I found it yesterday at Simple Talk.Or maybe it was the guy at Simple Talk who needed permissions:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/%5B/url%5D
Wow, they are identical!
No takers on the CLR function huh? C'mon, all the cool kids are trying it :Whistling:
How do you control the concatenation order of the strings using this CLR function?
The XML Path correlated subquery is one of the few places where you can use ORDER BY in the subquery and expect it to work correctly. And, no, you don't need to add a TOP to get it to work, either.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, He's questioning opc.three's script solution (not Lowell's XML solution).
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply