February 17, 2014 at 10:17 am
I've been struggling with this on my own for a week now and I've finally come to the realization that I'm not skilled enough to fight this battle alone so hopefully you all can help me out.
I wrote the following syntax in SQL Server 2008 to give me column data in a single row and tilde delimited (ex: "1234~4546~484~48464~484"). And alone it works perfectly:
DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + '~','') + (CASE WHEN X.code is NULL then 'NULL' ELSE code END)
FROM WorkingX
WHERE Fid = 12345
SELECT @combinedString as StringValue
Now I've been informed that there are other ways to convert columns to rows but because I have no way of knowing how many columns might be involved with each query it was made clear to me that this is the best way to do it.
But no matter how I add it to a larger select statement so that it will gather all the codes together on a person by person basis I get all manner of error. At first I tried:
Select H.Name as [Name],
H.Num as [Number],
@combinedString = COALESCE(@combinedString + '~','') + (CASE WHEN X.code is NULL then 'NULL' ELSE code END)
FROM H WITH(nolock) INNER JOIN
X WITH(nolock) on H.key=X.key
But I got the error message:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
So then I tred:
Select
H.Name as [Name],
H.Num as [Number],
(
SELECT @combinedString = COALESCE(@combinedString + '~','') + (CASE WHEN X.code is NULL then 'NULL' ELSE code END)
FROM WorkingX
WHERE Fid = 12345
)
FROM H WITH(nolock) INNER JOIN
X WITH(nolock) on H.key=X.key
And gotten the error:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I've noticed that SQL doesn't complain if I remove the "@combinedString = " syntax from the front of the coalesce statement. But doing that completely defeats the goal to create a singular row of data out of multiple columns.
I've searched the internet far and wide for answers and what I've discovered is that most of the people who have a problem with this type of coalesce statement usually turn to a different syntax entirely to achieve their results but in their cases they have a fixed number of records that they are gathering. Unfortunately I don't so it seems I need to do it this way...some how.
I hope I've provided enough details to clearly explain my problem - please let me know if I haven't, and thanks in advance to anyone who can point out what I'm doing wrong. 🙂
February 17, 2014 at 10:34 am
Could you try something like this: http://www.sqlservercentral.com/articles/comma+separated+list/71700/ ?
If you post DDL and sample data, we should be able to help you.
February 17, 2014 at 10:37 am
February 17, 2014 at 2:42 pm
Luis Cazares (2/17/2014)
Could you try something like this: http://www.sqlservercentral.com/articles/comma+separated+list/71700/ ?If you post DDL and sample data, we should be able to help you.
DDL? Do you mean direct download link, and if so to what exactly?
February 17, 2014 at 2:50 pm
Data Definition Language (CREATE statements for the tables and indexes you are using).
February 17, 2014 at 2:52 pm
robert.gerald.taylor (2/17/2014)
See if this helps: http://www.sql-server-helper.com/error-messages/msg-141.aspxHTH,
Rob
Thanks for the link. If I'm understanding it correctly it's basically saying to replace everything in the SELECT statement with assignments to local variables. I might be able to do this (even though it feels like it clutters up the code unnecessarily) but there's a case argument in my SELECT statement. How do I direct the following to a local variable:
(CASE WHEN H.Num='1234' then '5678' ELSE NULL END) as NUMBER,
February 17, 2014 at 2:52 pm
DDL? Do you mean direct download link, and if so to what exactly?
No, you need to provide scripts for creating tables from your sample query and fill them with a few rows. Here is an explanation how and why to do this: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
___________________________
Do Not Optimize for Exceptions!
February 17, 2014 at 3:01 pm
milos.radivojevic (2/17/2014)
DDL? Do you mean direct download link, and if so to what exactly?
No, you need to provide scripts for creating tables from your sample query and fill them with a few rows. Here is an explanation how and why to do this: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Ah, okay. Thanks for the clarification. I'll see what I can put together. 🙂
February 17, 2014 at 3:39 pm
Colonel_Derp (2/17/2014)
Luis Cazares (2/17/2014)
Could you try something like this: http://www.sqlservercentral.com/articles/comma+separated+list/71700/ ?If you post DDL and sample data, we should be able to help you.
DDL? Do you mean direct download link, and if so to what exactly?
Sorry for the confusion.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply