Post reply

New line Character in column alias

  • August 16, 2006 at 1:34 am

    #113268

    Hi,

        I have a query with column alias:

    select cust_ID as "Customer Number", Cust_Name as "Customer Name" from cust

    The output is as follows:

    Customer Number          Customer Name

    1                                 XFXFXFXF

    2                                  CFCFCFCF

     

    How can i get the Column Alias to look like this:

    Customer                   Customer

    Number                     Name

    1                            XFXFXFXF

    2                            CFCFCFCFCF

  • August 16, 2006 at 2:45 am

    #655211

    Yes..? And what is the question?

    /Kenneth

  • August 16, 2006 at 2:58 am

    #655213

    Hi Kenneth,

                       I have updated my initial post with the missing info. Please advice.

     

    Thanks

    Ravi

     

  • August 17, 2006 at 3:06 am

    #655393

    Well, never seen that requested before.

    AFAIK, it can't be done directly in the query. Column headers are returned on a single line.

    Perhaps do the formatting 'at the client/recieving' end?

    There may be ways to 'cheat', though you need to explain more about what you're trying to do - where the result is going and how it's going to be used.

    /Kenneth

  • August 17, 2006 at 5:10 am

    #655409

    Hi,

          Me too.

                 I would need the output to a text file. The end user wants to see a neatly formatted output.

     

    Regards

    Ravi

     

     

  • August 17, 2006 at 5:31 am

    #655412

    Hmmm.. well, endusers seldom knows what the meaning of 'neat' is...

    Only thing I can think of straight up is to generate the file in two steps, that is if it's still going to be done in T-SQL.

    Depending on how the file is actually written details may vary, but you could do something like this, briefly described using OSQL for writing to file

    1) create the file with only the first row, which is to be the 'top' of the columnnames.

    osql Q"SELECT 'Customer', 'Customer'" >> c:\myFile.txt

    2) The 'rest' of the query, but with columnnames that is the bottom row of the headers

    osql Q"SELECT col1 as 'number', col2 as 'name' FROM myTable" > c:\myFile.txt

    ..that's about it.

    The idea is to lay out the 'top header row' first, and then append the 'real' query results to the file.

    Note the differences between the first >> c:\myFile.txt and the second > c:\myFile.txt

    '>>' creates a file, '>' appends to an existing file. These are standard DOS redirection operators.

    Well, unfortunately, to keep your neat user neat, is going to be ugly at best. It can be done, but it won't look pretty, and you're likely to run into more formatting problems if he/she changes what's going into the report...

    Best of luck.

    =;o)

    /Kenneth

     

  • August 17, 2006 at 7:45 am

    #655451

    Thanks Ken!

  • August 17, 2006 at 7:56 am

    #655454

    Here's another (but similar) way:

    1. Put the SQL code in a stored procedure

    2. Run osql

    CREATE PROCEDURE uspCustFileOutput

    AS

    DECLARE @cust TABLE

    (

      cust_id int

    , cust_name varchar(30)

    )

    SET NOCOUNT ON

    INSERT @cust SELECT 1, 'Adams'

          UNION SELECT 2, 'Baker'

          UNION SELECT 3, 'Cain'

          UNION SELECT 4, 'Duncan'

    SET NOCOUNT OFF

    PRINT 'Customer' + Space(4) + 'Customer'

    SELECT cust_ID AS "Number"

         , Cust_Name AS "Name"

      FROM @cust

    GO

    From a command prompt, run:

    osql -S yourServer -E -d yourDB -Q "EXEC uspCustFileOutput" -o results.txt

     

Viewing 8 posts - 1 through 7 (of 7 total)

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