SQLTeam.com | Weblogs | Forums

SQL to transpose result onto single line

Hello Community,

Although I have stated 'Transpose' in the title of this topic, 'Transpose' may not be what I'm actually trying to achieve.

In column 'Resolution Description, see image, (Install at Timezone), I'm trying to write a query that will arrange the records in the cell onto one line.

If you look at the image, you will notice the highlighted cell, has the following records:

Date: 28.0.2019 2:16:44 +00:00-
Confirmed By: Garith Cashier
Resolving Step: Checked cabling
Traces saved on: N/A
Problem code updated: Yes

I would like help writing a query that will place on the records above onto one line like the following:

Date: 28.0.2019 2:16:44 +00:00-Confirmed By: Garith Cashier Resolving Step: Checked cabling Traces saved on: N/A Problem code updated: Yesru

Below is the table structure
CREATE TABLE SFBI.dbo.DAY_REPORT (
[Callback Notes (Install at Timezone)] VARCHAR(1950) NULL
,[Resolution Description (Install at Timezone)] VARCHAR(2100) NULL
,[Information Notes (Install at Timezone)] VARCHAR(4050) NULL
,[Interaction Notes (Install at Timezone)] VARCHAR(4050) NULL
,[Information Required Notes] VARCHAR(2450) NULL
,[Resolution Summary] VARCHAR(50) NULL
,ROID VARCHAR(50) NULL
) ON [PRIMARY]
GO

Please let me know if you need any furhter information from me to help me with this query

Carlton

Any help will be greatly appreciated guys

Is it a tool tip

Which needs to be added to the column

Hi harishgg1

Thanks for reaching out.

I forgot to mention that the reason the data is stored the way I described is because the data is stored as a carriage return.

Someone suggested doing the following, but I get the code to work

SELECT REPLACE([Resolution Description (Install at Timezone)] ,char(13)+char(10),' ') Resolution Description (Install at Timezone)

FROM DAY_REPORT

Please see this link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a2fab5d1-42b9-4ce7-8d45-37f5296701f7/how-to-remove-line-feed-or-carriage-return-from-varchar-or-nvarchar-column?forum=transactsql

I've been trying the following from the link you provided, but with no luck

select replace(theColumn, char(13) + char(10), '') from yourTable

I tried the following:

SELECT
REPLACE(DAY_REPORT.[Resolution Description (Install at Timezone)], CHAR(13) + CHAR(10), ' ') AS expr1
FROM dbo.DAY_REPORT

But still getting carriage return

Quite possible

Other characters are there
Along with

Not sure what you mean here?

What I mean is example

Carriage return... some other character here

Other way could be

Extract only text number characters
From the column

Experts on this forum... please help

Apparently, the following should work, but it still gives me carriage returns:

SELECT
REPLACE(DAY_REPORT.[Resolution Description (Install at Timezone)], CHAR(13) + CHAR(10), ' ') AS Resolution
FROM dbo.DAY_REPORT

Maybe this will work

If the column data type is text then you need to cast to nvarchar first then replace SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')

Carltonp

Did my last suggestion

Cast as nvarchar before replacing
Work

Just noticed your suggestion.

Going to try it right now..

Thanks

Hi Harishgg1, before I your suggestion. Don't I need to declare @str as string first?

Instead of @str just put your column

SELECT REPLACE(REPLACE(
cast( column as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
From table

1 Like

I have intepreted your code as follows:

replace (replace (cast (dbo.DAY_REPORT.[Resolution Description (Install at Timezone)] as nvarchar(max)), char(13), "), char(10), ").
However, I'm getting invalid syntax

IT WORKED!
SELECT REPLACE(REPLACE(
cast(DAY_REPORT.[Resolution Description (Install at Timezone)] as nvarchar(max)), CHAR(13), ''), CHAR(10), '')
From dbo.DAY_REPORT

Thanks Harishgg1.. You absolute genius!