Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Building a Comma Separated on a new line Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 8:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
Hi All,

I need some help! firstly to tweak my results to work as I expect it to & secondly provide an alternate method if my logic is not best practice.

I'm returning a list of PDF's (very minimal results < 20) into a temporary table and then running the following query to build these into a string. (E.G. 1.pdf,2.pdf,3.pdf ... 20.pdf)

DECLARE @PDFFileName VARCHAR(8000) = 
(
SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf'
FROM @PDFResult
ORDER BY 1 ASC FOR XML PATH('')),1,1,'')
)


My aim is to split these up 1 per line instead of a massive line (as below)

1.pdf,
2.pdf
...
20.pdf

Any ideas?
Post #1501401
Posted Thursday, October 3, 2013 9:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
forgot to add some important details. reason I'm doing it this way is because I'm passing the variable @PDFFileName into a email template using:

REPLACE(REPLACE(HTML,'[[FileName]]',@ProcessingFile),'[[PDF_FileName]]',@PDFFileName)



Post #1501405
Posted Friday, October 4, 2013 2:00 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,083, Visits: 31,624
Tava (10/3/2013)
Hi All,

I need some help! firstly to tweak my results to work as I expect it to & secondly provide an alternate method if my logic is not best practice.

I'm returning a list of PDF's (very minimal results < 20) into a temporary table and then running the following query to build these into a string. (E.G. 1.pdf,2.pdf,3.pdf ... 20.pdf)

DECLARE @PDFFileName VARCHAR(8000) = 
(
SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf'
FROM @PDFResult
ORDER BY 1 ASC FOR XML PATH('')),1,1,'')
)


My aim is to split these up 1 per line instead of a massive line (as below)

1.pdf,
2.pdf
...
20.pdf

Any ideas?


Try this:

DECLARE @PDFFileName VARCHAR(8000) = 
(
SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)
FROM @PDFResult
ORDER BY 1 ASC FOR XML PATH('')),1,1,'')
)




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1501454
Posted Monday, October 7, 2013 4:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
I read about using the CHAR(13) + CHAR(10) and i tried that but no difference.... I then read to just use CHAR(10) and that also failed to work.

There were no errors, just didnt work as expected.
Post #1502406
Posted Monday, October 7, 2013 4:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,374, Visits: 7,301
Where are you checking the results? The grid from SSMS won't show line feeds.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502413
Posted Tuesday, October 8, 2013 7:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 7:51 AM
Points: 60, Visits: 894
Try this

DECLARE @PDFFileName VARCHAR(8000) =
(
SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)
FROM @PDFResult
ORDER BY 1 ASC FOR XML PATH(''),type).value('(./text())[1]','Varchar(8000)'),1,1,'')
)
Select @PDFFileName
Post #1502586
Posted Tuesday, October 8, 2013 3:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
Luis Cazares (10/7/2013)
Where are you checking the results? The grid from SSMS won't show line feeds.


I'm checking the results in the actual out of the email, not the grid in SSMS.
Post #1502852
Posted Tuesday, October 8, 2013 4:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,786, Visits: 5,682
It sounds to me like your email may have an html body, in which case, replace the char(13), char(10) combination with a '< br / >' (remove the spaces) tag, which has the same effect on an html page.

MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1502855
    Posted Tuesday, October 8, 2013 4:11 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    Edward Boyle-478467 (10/8/2013)
    Try this

    DECLARE @PDFFileName VARCHAR(8000) =
    (
    SELECT STUFF(( SELECT ', ' + PDF_FileName + '.pdf' + char(13) + char(10)
    FROM @PDFResult
    ORDER BY 1 ASC FOR XML PATH(''),type).value('(./text())[1]','Varchar(8000)'),1,1,'')
    )
    Select @PDFFileName



    hmmm, it didn't work still displays in my email as a long concatenated string " a.pdf,b.pdf,c.pdf "

    I'm not too fussed about getting it working as it doesn't look too bad but now I'm just curious for a solution.



    Post #1502859
    Posted Tuesday, October 8, 2013 4:16 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Sunday, April 27, 2014 8:38 PM
    Points: 90, Visits: 420
    mister.magoo (10/8/2013)
    It sounds to me like your email may have an html body, in which case, replace the char(13), char(10) combination with a '< br / >' (remove the spaces) tag, which has the same effect on an html page.


    Yeah it is HTML body, I've tried using the br or p syntax and no difference.
    Post #1502862
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse