Formatted ASCII Results from a SELECT

  • tstoneberg

    SSC Rookie

    Points: 30

    bevanward - Monday, January 22, 2018 7:32 AM

    andrew.ing - Monday, January 22, 2018 3:54 AM

    Hello Bevan,

    In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.

    The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.

    Thanks again for this.  It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.

    Andrew

    Thanks Andrew
    Thanks for all of this.
    Have edited the code to use your cast suggestion and now the execution does not hang up on the residual working table. I've resubmitted the edit so hopefully it will replace the original post.

    Very glad you like it

    Keep well - thanks for the ASCII collaboration!

    Cheers
    Bevan

    FWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather  than the other way around.
    I changed the relevant line to.

    select @SQL = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' from

    I do have one question. There is no order by in the string of unions that you use to output your result.

    select @top Output union all
    select @columnnames union all
    select @top x union all
    select Body from temptxt3 union all
    select @top

    In times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this


    SELECT Output FROM (
    select Output = @top, Ord = 1  union all
    select @columnnames,2 union all
    select x = @top, 3  union all
    select Body, 4 from temptxt3 union all
    select @top, 5) x
    ORDER BY Ord

  • bevanward

    SSC Eights!

    Points: 855

    tstoneberg - Monday, January 22, 2018 1:47 PM

    bevanward - Monday, January 22, 2018 7:32 AM

    andrew.ing - Monday, January 22, 2018 3:54 AM

    Hello Bevan,

    In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.

    The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.

    Thanks again for this.  It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.

    Andrew

    Thanks Andrew
    Thanks for all of this.
    Have edited the code to use your cast suggestion and now the execution does not hang up on the residual working table. I've resubmitted the edit so hopefully it will replace the original post.

    Very glad you like it

    Keep well - thanks for the ASCII collaboration!

    Cheers
    Bevan

    FWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather  than the other way around.
    I changed the relevant line to.

    select @SQL = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' from

    I do have one question. There is no order by in the string of unions that you use to output your result.

    select @top Output union all
    select @columnnames union all
    select @top x union all
    select Body from temptxt3 union all
    select @top

    In times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this


    SELECT Output FROM (
    select Output = @top, Ord = 1  union all
    select @columnnames,2 union all
    select x = @top, 3  union all
    select Body, 4 from temptxt3 union all
    select @top, 5) x
    ORDER BY Ord

    Hi tstoneberg
    Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!

    To get a sort working you would have to use a two stage sort - something like

    /* return stuff */
    select Output from
    (select @top Output, 1 Base, 1 Sec union all
    select @columnnames, 2, 2 union all
    select @top, 3, 3 union all
    select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
    select @top, 5, 1) x
    order by Base, Sec

    Does that make sense?
    I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.

    Thanks
    Bevan

  • tstoneberg

    SSC Rookie

    Points: 30

    bevanward - Monday, January 22, 2018 11:50 PM

    tstoneberg - Monday, January 22, 2018 1:47 PM

    bevanward - Monday, January 22, 2018 7:32 AM

    andrew.ing - Monday, January 22, 2018 3:54 AM

    Hello Bevan,

    In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.

    The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.

    Thanks again for this.  It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.

    Andrew

    Thanks Andrew
    Thanks for all of this.
    Have edited the code to use your cast suggestion and now the execution does not hang up on the residual working table. I've resubmitted the edit so hopefully it will replace the original post.

    Very glad you like it

    Keep well - thanks for the ASCII collaboration!

    Cheers
    Bevan

    FWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather  than the other way around.
    I changed the relevant line to.

    select @SQL = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' from

    I do have one question. There is no order by in the string of unions that you use to output your result.

    select @top Output union all
    select @columnnames union all
    select @top x union all
    select Body from temptxt3 union all
    select @top

    In times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this


    SELECT Output FROM (
    select Output = @top, Ord = 1  union all
    select @columnnames,2 union all
    select x = @top, 3  union all
    select Body, 4 from temptxt3 union all
    select @top, 5) x
    ORDER BY Ord

    Hi tstoneberg
    Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!

    To get a sort working you would have to use a two stage sort - something like

    /* return stuff */
    select Output from
    (select @top Output, 1 Base, 1 Sec union all
    select @columnnames, 2, 2 union all
    select @top, 3, 3 union all
    select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
    select @top, 5, 1) x
    order by Base, Sec

    Does that make sense?
    I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.

    Thanks
    Bevan

    Yes that does make sense. In my case I wasn't worried about the order of the input selection only header and footer.

  • bevanward

    SSC Eights!

    Points: 855

    tstoneberg - Tuesday, January 23, 2018 6:59 AM

    bevanward - Monday, January 22, 2018 11:50 PM

    tstoneberg - Monday, January 22, 2018 1:47 PM

    bevanward - Monday, January 22, 2018 7:32 AM

    andrew.ing - Monday, January 22, 2018 3:54 AM

    Hello Bevan,

    In my haste I didn't try a null date, but you're right of course. Converting to varchar first means that no date or other type is presented to IsNull.

    The only other change I made in use was to ensure that the working tables are always dropped. Any error in the SQL parameter will prevent the original procedure from cleaning up, and it became annoying having to delete three tables manually each time.

    Thanks again for this.  It's a nice alternative to taking screenshots of datasets, which isn't helpful to the end recipient, or taking a detour via (the very un-cooperative) Excel to format columns for pasting.

    Andrew

    Thanks Andrew
    Thanks for all of this.
    Have edited the code to use your cast suggestion and now the execution does not hang up on the residual working table. I've resubmitted the edit so hopefully it will replace the original post.

    Very glad you like it

    Keep well - thanks for the ASCII collaboration!

    Cheers
    Bevan

    FWIW, I've been able to get it to work with GUIDs, DateTimes and Decimal data types without an extra cast, just by putting the cast inside the ISNULL rather  than the other way around.
    I changed the relevant line to.

    select @SQL = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')) collate database_default,'''')+''|''' from

    I do have one question. There is no order by in the string of unions that you use to output your result.

    select @top Output union all
    select @columnnames union all
    select @top x union all
    select Body from temptxt3 union all
    select @top

    In times past I've trusted this sort of thing to work and it does almost the time but then when I least expect it it returns a different order and everything is messed up. I've always resorted to something like this


    SELECT Output FROM (
    select Output = @top, Ord = 1  union all
    select @columnnames,2 union all
    select x = @top, 3  union all
    select Body, 4 from temptxt3 union all
    select @top, 5) x
    ORDER BY Ord

    Hi tstoneberg
    Yes - looks like you are right, makes sense - thanks I will make the change and save the cast!!

    To get a sort working you would have to use a two stage sort - something like

    /* return stuff */
    select Output from
    (select @top Output, 1 Base, 1 Sec union all
    select @columnnames, 2, 2 union all
    select @top, 3, 3 union all
    select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
    select @top, 5, 1) x
    order by Base, Sec

    Does that make sense?
    I was hoping the union all would save me from a silly order but is probably to do with how it is arranged on the disk so sorting is probably the most safe.

    Thanks
    Bevan

    Yes that does make sense. In my case I wasn't worried about the order of the input selection only header and footer.

    Hi tstoneberg
    For those pieces of the output they won't change order due to how union all functions. Union would be anyone's guess as that is resolving unique between all sets however union all is safe from my findings

    Cheers
    Bevan

  • mike.mihalik

    Grasshopper

    Points: 16

    Where is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.

  • bevanward

    SSC Eights!

    Points: 855

    mike.mihalik - Monday, February 5, 2018 5:45 PM

    Where is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.

    Hi Mike

    No idea what has happened to the procedure that was attached. I will ask the admin who can hopefully reattach - hopefully it is a quick fix.

    Thanks
    Bevan

  • bevanward

    SSC Eights!

    Points: 855

    mike.mihalik - Monday, February 5, 2018 5:45 PM

    Where is the procedure? The "Conclusion" section indicates it is attached, but I don't see a download link. Any help is appreciated.

    Mike in the mean time the code is as follows:

    Not sure if somehow I messed up however it was attached...
    Cheers
    Bevan

    if OBJECT_ID('dbo.sp_sqltable_to_text_format') is not null
        drop procedure dbo.sp_sqltable_to_text_format
    go

    create procedure dbo.sp_sqltable_to_text_format @SQL nvarchar(max)
    as

    /*

    Author:        Bevan Ward
    Date:        9-Dec-2017
    Purpose:    The following stored procedure turn SQL from a statement into ASCII format table
    Copyright:    Bevan Ward
    Version:    v2 added cast(['+COL+'] as nvarchar(max) to convert each field from original so that dates do not convert to Jan 1 1900, etc (big thanks to Andrew.ing)

    Syntax:        exec dbo.sp_sqltable_to_text_format @SQL = '<valid SQL Select statement>'
    Example:    exec dbo.sp_sqltable_to_text_format @SQL = 'select name, type_desc, CONVERT(VARCHAR(10), create_date, 112) create_date from sys.objects where year(create_date) = year(getdate()) and type = ''U'''

    Example:    ---- Using replicate to create a dotted line for editing
                exec dbo.sp_sqltable_to_text_format @SQL = 'select name, type_desc, CONVERT(VARCHAR(10), create_date, 112) create_date, replicate(''.'', 100) Comment from sys.objects where year(create_date) = year(getdate()) and type in (''U'',''V'')'

    Form of output:
    +--------+-------+ ---> -top line '+'( one dash, width of dashes,'+')
    | Field | field | ---> -column names
    +--------+-------+ ---> -base header line
    | value | value | ---> -body
    | value | value |
    +--------+-------+ ---> -bottom

    * make sure format text as non-scaled - courier new for example

    */

    set nocount on

    declare @top nvarchar(max)=''
    declare @columnnames nvarchar(max)=''

    create table temptxt3 (ID int identity(1,1), Body nvarchar(max))

    /* pump SQL into set table name */
    set @SQL = 'select * into temptxt1 from ('+@SQL+') x'
    exec (@SQL)

    /* clean out char-9,10,13 */

    set @SQL = ''
    select @SQL = @SQL + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(9),'' '') where [' + COLUMN_NAME + '] like ''%''+char(9)+''%'''
         + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(10),'' '') where [' + COLUMN_NAME + '] like ''%''+char(10)+''%'''
         + ' update temptxt1 set [' + COLUMN_NAME + '] = replace([' + COLUMN_NAME + '],char(13),'' '') where [' + COLUMN_NAME + '] like ''%''+char(13)+''%'''
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'temptxt1' and DATA_TYPE in ('nvarchar','nvarchar','char')
    exec (@SQL)

    --
    /* build table of ORDINAL_POSITION, COLUMN_NAME, and WIDTH (if null then 2) */
    set @SQL = ''
    select @SQL = Stuff((select ' ' + SEL
           from (select 'union all select ' + Cast(ORDINAL_POSITION as varchar(10)) + ' POS, isnull(max(len([' + COLUMN_NAME + '])),2)+1 WIDTH, ''' + COLUMN_NAME + ''' COL from temptxt1' SEL
              from INFORMATION_SCHEMA.COLUMNS
              where TABLE_NAME = 'temptxt1') x
           FOR XML PATH ('')), 1, 11, '')

    set @SQL='select POS, COL, case when len(COL) > WIDTH then len(COL)+1 else WIDTH end WIDTH into temptxt2 from ('+@SQL+')x'
    exec (@SQL)

    /*
    Construct output parts...
    */

    /* top line/base header line/bottom */
    select @top = '+'+stuff((select '-'+replicate('-',WIDTH)+'+' from
    (select top 100 percent * from temptxt2) x
    order by POS asc
    FOR XML PATH ('')), 1, 0, '')

    /* column names */
    select @columnnames = '|'+stuff((select ' '+COL+replicate(' ',WIDTH-len(COL))+'|' from
    (select * from temptxt2) x
    order by POS asc
    FOR XML PATH ('')), 1, 0, '')

    /* body */
    set @SQL = ''
    select @SQL = '''|'''+stuff((select '+'' ''+isnull(cast(['+COL+'] as char('+cast(WIDTH as varchar(10))+')),'''') collate database_default+''|''' from
    (select * from temptxt2) x
    order by POS asc
    FOR XML PATH ('')), 1, 0, '')

    /* write body into one a single field */
    set @SQL = 'insert into temptxt3 (Body) select * from (select '+@SQL+' Body from temptxt1) Text'
    exec(@SQL)

    /* return stuff */
    select Output from
    (select @top Output, 1 Base, 1 Sec union all
    select @columnnames, 2, 2 union all
    select @top, 3, 3 union all
    select Body, 4, row_number() over (order by (select null)) from temptxt3 union all
    select @top, 5, 1) x
    order by Base, Sec

    /* clean up */
    if OBJECT_ID('dbo.temptxt1') is not null
        drop table dbo.temptxt1

    if OBJECT_ID('dbo.temptxt2') is not null
        drop table dbo.temptxt2

    if OBJECT_ID('dbo.temptxt3') is not null
        drop table dbo.temptxt3

    if OBJECT_ID('dbo.temptxt3') is not null
        drop table dbo.temptxt3

    go

  • mike.mihalik

    Grasshopper

    Points: 16

    Thank you

  • skeleton567

    SSCertifiable

    Points: 5024

    Thanks, Bevan.  Some thing we can all use quite a bit.

    Rick

    The only thing worse than being an influencer
    is believing one.

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply