7

Here is what I've got currently when executing queries in sqlcmd:

1> SELECT 1,2,3,4
2> GO

----------- ----------- ----------- -----------
          1           2           3           4

(1 rows affected)

Although in case of longer results this output format is not human-readable, as it gets wrapped in the terminal.

How do I display the results vertically (an equivalent of \G in mysql)? Or in another way which can be human-readable?

  • 2
    do you have to use sqlcmd? I see mssql-cli states "Pretty formatting for query results, including Vertical Format" blogs.technet.microsoft.com/dataplatforminsider/2017/12/12/… – Martin Smith Feb 8 '18 at 17:03
  • 1
    @MartinSmith Yes, I can use alternatives, as far I can install them easily on Linux. (Yep, installed it fairly quickly, but got some CoreCLR errors, will dig it out) – kenorb Feb 8 '18 at 17:04
  • Cool. If you get it working would be good to see a screenshot of this vertical format. – Martin Smith Feb 8 '18 at 17:09
  • 2
    UNPIVOT – Michael Green Feb 9 '18 at 11:35
5

The expected result when you use SELECT F1, F2, F2 is to obtain one column for each field in the SELECT sentence.

SELECT '1' + CHAR(13) + '2' + CHAR(13) + '3' + CHAR(13)

------
1
2
3


(1 row(s) affected)
5

You could use UNION:

select 1 union select 2 union select 3 union select 4

Results:

-----------
1
2
3
4

(4 rows affected)
4

I found a pure T-SQL approach to Display results VERTICALLY in Results window. It involves a custom stored procedure (sp_SHOWDOWN) that I'm copying from this link. You could play around with it to see if it helps.

The author does indicate a few limitations:

  • The query can be as complex as necessary with as many joins however the column names must be unique because of the temp table.
  • It will be very slow if you are trying to return a lot of records. Works best for queries returning 10 or less records.
  • It displays the DATALENGTH for text and image fields

Here is a brief description (taken from the post):

This procedure will allow you to display results vertically (down) instead of across the screen. It's great when you are dealing with a query that has 50 columns and just a couple of records

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/********************************************************************************************************
** NAME: sp_ShowDown
**
** DESC: Display (SHOWs) the results of a SELECT vertically (DOWN) instead of horizontally  
**       The query can be as complex as necessary with as many joins however the column names
**       must be unique because of the temp table.  Note image and text fields display only 
**       their size (DATALENGTH)
**
** PARM: @Help = 1 will display syntax and instructions
**
** RETR: the resultset of the records.  Notes displaying a lot of records will take a LONG time.  Generally
**       this should be used for recordsets of no more than 10.  
**
** AUTH: D. Simmons
**
** SYNTAX  sp_showdown 1 -- displays full syntax on how to run
**
** MOD DATE:
** 05.22.07 - DTS Prevent casting of image & text to varchar
** 05.20.07 - DTS original version
*********************************************************************************************************/
ALTER PROCEDURE [dbo].[sp_ShowDown] (
    @help   BIT = NULL
)
AS

SET NOCOUNT ON

    -- ------------------------------------------------------------------------
    -- DECLARATION AND TABLE CREATION
    -- ------------------------------------------------------------------------

    DECLARE 
        @Column     VARCHAR(60),        -- the fieldname
        @CurrOrdPos INT,                -- the order of the column in the table
        @SQL        VARCHAR(1000),      -- dynamic select statement
        @SQ         CHAR(1),            -- single quote
        @MaxTable   VARCHAR(1000),      -- holds the tempwide2 name - the true one stored in tempdb
        @RecordID   INT,                -- each record's number to aid in sorting when more than one record is return
        @DataType   VARCHAR(25),        -- the datatype of the field
        @FieldName  VARCHAR(200)            -- will hold column's name with brackets ready for the SELECT               

    IF OBJECT_ID('tempdb..#tempdown') IS NOT NULL DROP TABLE #tempdown

    CREATE TABLE #tempdown (
        Rec         INT,                -- short column names on purpose so it doesn't take up much 
        Ord         INT,                -- space in final result
        ColumnName  VARCHAR(60),        -- the columnname 
        Data        VARCHAR(7500)       -- the data for the column
    )

    -- ------------------------------------------------------------------------
    -- INITIALIZE
    -- ------------------------------------------------------------------------

    SET @RecordID = 0

    -- CONSTANTS
    SET @SQ = CHAR(39)      -- single quote


    -- ------------------------------------------------------------------------
    -- LOGIC
    -- ------------------------------------------------------------------------ 

    -- print the syntax and usage instructions to the result window
    IF @Help = 1 BEGIN
        PRINT 'Keep in mind that with temp tables the column names must be unique!'
        PRINT ' '
        PRINT 'Example of syntax: '
        PRINT ' '
        PRINT 'IF OBJECT_ID(''tempdb..#tempwide'') IS NOT NULL DROP TABLE #tempwide  -- ADD TO TOP OF YOUR SELECT'
        PRINT ' '
        PRINT 'SELECT TOP 1 * '
        PRINT 'INTO #tempwide           -- ADD THIS TO YOUR QUERY'
        PRINT 'FROM authors a'
        PRINT ' ' 
        PRINT 'EXEC _SHOWDOWN               -- ADD AS THE LAST LINE'
        PRINT '  '                         
        PRINT 'COPY THESE LINES and place where instructed'
        PRINT 'IF OBJECT_ID(''tempdb..#tempwide'') IS NOT NULL DROP TABLE #tempwide'
        PRINT 'INTO #tempwide'
        PRINT 'EXEC sp_SHOWDOWN'

        RETURN
    END

    -- Create a new 'wide' table so we can add a RecordID (DIDROCER) which allows muliple records and their fields 
    -- to be grouped together.  DIDROCER is RecordID backwards.  Needed a field name that will have an unlikely
    -- chance of ever being in a real table since it will be excluded from the results displayed vertically.
    SELECT  0 'DIDROCER', *
    INTO    #tempwide2
    FROM    #tempwide

    -- increment the record id for the table
    UPDATE  #tempwide2 SET  @RecordID = DIDROCER = @RecordID + 1

    -- get name of tempwide2 table (the true name in tempdb)
    SET @MaxTable = (   SELECT  MAX(TABLE_NAME) 
                        FROM    tempdb.INFORMATION_SCHEMA.TABLES
                        WHERE   Table_Name LIKE '%#tempwide2%'
                    )

    -- get the min ord position for the first column for my temp table.  Eliminates need for cursor
    SET @CurrOrdPos = ( SELECT  MIN(Ordinal_Position) 
                        FROM    tempdb.INFORMATION_SCHEMA.COLUMNS 
                        WHERE   Table_Name LIKE '%' + @MaxTable + '%' )


    -- while we have columns in the temp table loop through them and put their data into the 
    -- tempdown table
    WHILE @CurrOrdPos IS NOT NULL BEGIN 

        -- get a column name and the data type
        SELECT  @Column = COLUMN_NAME, @DataType = Data_Type
        FROM    tempdb.INFORMATION_SCHEMA.COLUMNS 
        WHERE   Table_Name LIKE '%' + @MaxTable + '%' 
        AND     Ordinal_Position = @CurrOrdPos 


        IF @Column <> 'DIDROCER' BEGIN      -- if it is not the recordid (spelled backward) row from tempwide2 get the row


            IF @DataType IN ( 'image', 'text' ) BEGIN
                -- 'Size of Data: ' + CONVERT(VARCHAR(15), DATALENGTH([NoteText] )) 
                SET @FieldName = @SQ + 'Size of Data: ' + @SQ + ' + CONVERT(VARCHAR(15), DATALENGTH(' + @FieldName + ')) '
            END ELSE BEGIN
                SET @FieldName = 'CAST( [' + @Column + '] AS VARCHAR(7500) )'           -- the fieldname w/ brackets used in SELECT to display the data
            END

            -- build the insert that will put the data into the tempdown table
            SET @SQL = ' INSERT INTO #tempdown ' 
            SET @SQL = @SQL + 'SELECT didrocer ' + @SQ + 'RecordID' + @SQ + ', '        -- recordid field from tempwide2 table
            SET @SQL = @SQL + CONVERT(VARCHAR(10), @CurrOrdPos) + ', '                  -- order of the column
            SET @SQL = @SQL + @SQ + @Column + @SQ + ' ' + @SQ + 'Field' + @SQ + ', '    -- field name 
            SET @SQL = @SQL + @FieldName + @SQ + @Column + @SQ                          -- field data
            SET @SQL = @SQL + ' FROM ' + @MaxTable                                      -- from tempwide2
        END

        --@SQL above looks like this:
        --INSERT INTO #tempdown SELECT DIDROCER 'RecordID', 5, 'UserID' 'Field', [UserID] 'UserID' FROM #tempwide2 {shorten}_____00010000003F
        --PRINT @SQL

        EXEC ( @SQL )       -- run the insert into #tempdown

        -- get the next column pos
        SET @CurrOrdPos = ( SELECT  MIN(Ordinal_Position) 
                            FROM    tempdb.INFORMATION_SCHEMA.COLUMNS 
                            WHERE   Table_Name LIKE '%' + @MaxTable + '%'
                                AND Ordinal_Position > @CurrOrdPos)


    END

    -- display the results VERTICALLY!
    SELECT  ColumnName, Data FROM   #tempdown ORDER BY Rec, Ord, ColumnName

    -- clean up
    IF OBJECT_ID('tempdb..#tempdown') IS NOT NULL DROP TABLE #tempdown
    IF OBJECT_ID('tempdb..#tempwide') IS NOT NULL DROP TABLE #tempwide
    IF OBJECT_ID('tempdb..#tempwide2') IS NOT NULL DROP TABLE #tempwide2

Setting up a test bed of data

--Setup testbed of data
DROP TABLE IF EXISTS dbo.customer
CREATE TABLE [dbo].[Customer] (
    [CustomerID] [int] NULL
    ,[Name] [varchar](30) NULL
    ,[RecordCreated] [datetime] NULL
    ,[RecordUpdated] [datetime] NULL
    ,
    ) ON [PRIMARY]
GO

INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO

Here is the actual process to return the results in vertical format

IF OBJECT_ID('tempdb..#tempwide') IS NOT NULL DROP TABLE #tempwide

SELECT *
INTO #tempwide -- ADD THIS TO YOUR QUERY
FROM customer a

EXEC sp_SHOWDOWN -- ADD AS THE LAST LINE

Here are the results

| CustomerID    | 1                   |
|---------------|---------------------|
| Name          | James               |
| RecordCreated | Nov  1 2017  4:16PM |
| RecordUpdated | Nov  1 2017  4:52PM |
| CustomerID    | 2                   |
| Name          | John                |
| RecordCreated | Nov  1 2017  4:41PM |
| RecordUpdated | Nov  1 2017  4:41PM |
| CustomerID    | 3                   |
| Name          | Sam                 |
| RecordCreated | Nov  1 2017  4:50PM |
| RecordUpdated | Nov  1 2017  4:50PM |
| CustomerID    | 1                   |
| Name          | James               |
| RecordCreated | Nov  1 2017  4:16PM |
| RecordUpdated | Nov  1 2017  4:52PM |
| CustomerID    | 2                   |
| Name          | John                |
| RecordCreated | Nov  1 2017  4:41PM |
| RecordUpdated | Nov  1 2017  4:41PM |
| CustomerID    | 3                   |
| Name          | Sam                 |
| RecordCreated | Nov  1 2017  4:50PM |
| RecordUpdated | Nov  1 2017  4:50PM |

Update (2018-02-09)

After posting my answer, Martin Smith added a comment that referenced a fantastically simple approach to this problem by using FOR XML PATH and a couple of CROSS APPLY's.

I'm including his solution here in case the SQL Fiddle link goes dead.

SELECT n.value('local-name(.)', 'SYSNAME') AS Col,
       n.value('.', 'nvarchar(4000)')
FROM Customer c
CROSS APPLY 
(SELECT c.*
 FOR XML PATH('r'), TYPE) ca(x)
 CROSS APPLY ca.x.nodes('/r/*') n(n)
  • 1
    There will definitely be simpler ways to implement that proc. E.g. this sqlfiddle.com/#!18/8f0a9/10/0 (though it doesn't currently handle ordering or all datatypes but shows the idea) – Martin Smith Feb 8 '18 at 22:42
2
select v 
from ( values (1), (2), (4)
     ) val(v)
2

If you are dealing with fields that are not varchar/nvarchar and still want the simplicity of [Field] + char(13), you can skip the CONVERT/CAST boilerplate by using CONCAT (SQL v2012+), which should handle whatever you throw at it

https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql


SELECT CONCAT(1, char(13), GETDATE(), char(13), 3.0, char(13), 'A')
-------------------------------------------
1
Feb  9 2018 11:39AM
3.0
A

(1 row affected)

*CONCAT is limited to 254 parameters

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.