Commas in Result Pane
-
I need to add literal commas in the result output of Query Analyzer.
Currently the resudlts are:
06630121501Transmitted10002/20/2006200602
and I need:
0663012150,1,Transmitted,100,02/20/2006,200602
This is the current script:
Select r.ClientRespondentID, i.HMR_EventID, i.HMR_EventDesc, h.HMR_DetailEventID,
CONVERT(VARCHAR,e.EventDate,101) AS EventDate,
CONVERT(VARCHAR,YEAR(r.NextContact)) + RIGHT('00' + CONVERT(VARCHAR,MONTH(r.NextContact)),2) AS NextContact
from (
Select t.respondentid, max(e.EventLogID) as maxLogID FROM (
Select s.respondentid,max(CONVERT(VARCHAR,e.EventDate,101))as eventDate from
(select RespondentID from Respondents WHERE surveyinstanceid in (2871, 2872)) s,eventlog e
WHERE s.respondentid = e.respondentid
GROUP By s.RespondentID)
t, EventLog e
WHERE t.RESPONDENTID = e.respondentid
AND t.eventdate = CONVERT(VARCHAR,e.EventDate,101)
GROUP BY t.respondentid)
t2 INNER JOIN EventLog e ON t2.maxLogID = e.EventLogID
INNER JOIN Respondents r ON r.RespondentID=e.RespondentID
Inner JOIN HMR_MergedCodes h ON h.SurveyPointEvent_Id = e.EventID
Inner JOIN HMR_MergedCodesDetails i on h.HMR_DetailEventID = i.HMR_DetailEventID
Any help would be great.
Thanks
Art - NRC
-
What if you formatted each output col like this:
COALESCE(yourCol, '') + ','
Or am I being overly simplistic? let me know
Thanks!
SQL guy and Houston Magician
-
Why not bcp the data out in a comma delemited format?. Will save you some uneeded data manipulations!
-
They want to see the visible results fro Query Analyzer otherwise I would have just DTSed it out.
-
The users using the application need to see the results in QA??
I'm not following here
.So far Robert's solution seems to be the only viable path, especially if you have to do it in QA.
-
This works great up to the point where the column is an integer:
COALESCE(yourCol, '') + ','
And ideas?
-
If you want that result in query analyser result pane in the comma delimited format then you can do this by setting these option ...
Tool --> Options --> Results -- > "Result Output Format (*)" ='Comma Deliminited CSV'
and make the output type as TEXT.
cheers
-
ijaz raises a good point!
In case it's not what you're after (but it probably is and is a great solution), here is the answer to your question.
COALESCE(CAST(YourIntCol AS VARCHAR),'') + ','
Conversions in your select have to run for every single row returned, so it add some cost. From your posts, though, I doubt this is going to become production code so don't worry too much.
SQL guy and Houston Magician
-
Journeyman,
If I turn the options to
Tool --> Options --> Results -- > "Result Output Format (*)" ='Comma Deliminited CSV'
and make the output type as TEXT. as
you suggested. My question is if I save that query will the option be saved also or is it something that needs to be set everytime I open the QA file?
-
It's a very good question. We you test it, can you post the results to us?
TIA.
-
QA only stores the text of the query in the .sql file
SQL guy and Houston Magician
-
Where going with the turning the options to Comma delimited but with the addition of double Quoted identifiers (""). I can't seem to fine that option. Any ideas?
-
Good, I'm glad you've found a solution!
I don't know any way to add the double quotes either (short of doing it in the query). If anyone finds a way to, please post the answer!
SQL guy and Houston Magician
-
Use a custom delimeter.
-
SELECT QUOTENAME('abc[]def', '"')
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply