carriage return in stored proc
-
hi,
i have a sp that has 1 field which retrieves procedure1, a 2nd field which retrieves procedure2(there are not always 2 different procedures) but if there are I need to have them in one field. I would like to use a carriage return so it displays below the other.I am trying to obtain the following results from my sp.
ProcedureII PROCEDURE PROCEDURE_
Hysterectomy after VD D&C/D&E Hysterectomy after VD
D&C/D&E----D&C
my results display as (wrapped in one line)
ProcedureII PROCEDURE PROCEDURE_
Hysterectomy after VD D&C/D&E Hysterectomy after VD D&C/D&E----D&C
this is my query
SELECT DISTINCT
T1.SessionID,
T1.Neonate#,
T1.[Total Neonates],
T1.[Last Name],
T1.[First Name],
T1.MRN,
T1.Account,
T1.[Delivery Date],
T1.[Delivery Type],
T1.Anesthesia,
T1.[Anesth. Code],
T1.Complications,
T1.Lacerations,
T1.[PACU Code],
--T1.PACU2,
T1.CDM,
T1.[Procedure],
T1.Proc_Charge,
T2.[PROCEDURE] AS II_PROCEDURE,
case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then T1.[Procedure]
when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)
then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when T2.[PROCEDURE] is null and T1.[Procedure] is not null then T1.[Procedure]
when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then T1.[Procedure]
when T1.[PROCEDURE] is null and T2.[Procedure] is not null then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
[highlight="#ffff11"]when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then T1.[PROCEDURE]+CHAR(13)+CHAR(10)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )[/highlight]
else null
end as PROCEDURE_,
T2.CATEGORY AS II_CATEGORY,
T2.CDM AS II_CDM,
T2.PACU AS II_PACU,
T2.ANESTHESIA AS II_ANESTHESIA,
T2.ANESTH_CHARGE AS II_ANESTH_CHARGE,
T1.[Adhesive Barrier],
T1.Manufacturer,
T1.CatalogNumber,
T1.LotNumber,
T1.Qty,
T1.[Implant Charge],
T2.[TIME] AS II_DATE,
T1.FacilityName
FROM
DBO.CHARGECAPTURE T1
LEFT OUTER JOIN
DBO.CHARGECAPTURE2 T2
ON T1.SESSIONID = T2.SESSIONID
WHERE T1.entrytime BETWEEN '01/01/2013' AND '08/20/2016'
OR T2.[TIME] BETWEEN '01/01/2013' AND '08/20/2016'
what am I doing incorrectly, how do I obtain my desired result???
-
you are doing it correctly, but it depends on what is doing the presenting.
In Grid mode in Management Studio, CrLf are ignored(converted to spaces) FOR PRESENTATION ONLY, if you copy paste to notepad or excel, the CrLf exists.
so if a web page or applciaiton received the data, it contains the CrLf as expected.
If you expect to see it as a new line in SSMS, it cannot happen; maybe if you wnet to text mode, but nothing would align the way you might expect.
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! -
SQL doesn't work like EXCEL, where a cell has two lines in it. SQL only stores values. If you want to preserve both values, I strongly advise you to have two separate columns in the output result set. How you want to present it should be controlled by the calling application program.
Edited to add: Dangit, Lowell you were too quick for me. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
thank you, I decided to use a --&-- to separate the values
-
Are you ever going to have to work with those values from the result set again? Or is displaying them the end of their usefulness?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Good possibility.
-
Then I urge you again to store them in two columns of your resultset rather than one, otherwise you will have to split them out to work on them again. Think about the difference between storing data and presentation. Only when you reach the point of final presentation do you need to add your '-&-'. In my experience it is always easier to stick things together than to split them apart. But either way, best of luck to you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply