TSQL -- display results of two queries on one row in SSMS

Question

I am using TSQL, SSMS v.17.9.1 The underlying db is Microsoft SQL Server 2014 SP3

For display purposes, I want to concatenate the results of two queries:

SELECT TOP 1 colA as 'myCol1' FROM tableA
--
SELECT TOP 1 colB  as 'myCol2' FROM tableB

and display the results from the queries in one row in SSMS. (The TOP 1 directive would hopefully guarantee the same number of results from each query, which would assist displaying them together. If this could be generalized to TOP 10 per query that would help also)

Have you searched for `[tsql] concatenate`? Using subqueries, rather than `union`, lets you get the results in a single row. — HABO, Nov 08 '19 at 17:51

BarneyL · Accepted Answer · 2019-11-08T20:10:17.850

1

This should work for any number of rows, it assumes you want to pair ordered by the values in the column displayed

        With 
        TableA_CTE AS
        (
        SELECT TOP 1 colA as myCol1
        ,Row_Number() OVER (ORDER BY ColA DESC)  AS RowOrder 
        FROM tableA
        ),
        TableB_CTE AS
        (
        SELECT TOP 1 colB as myCol2
        ,Row_Number() OVER (ORDER BY ColB DESC)  AS RowOrder 
        FROM tableB
        )
        SELECT A.myCol1, B.MyCol2
        FROM TableA_CTE AS A
        INNER JOIN TableB_CTE AS B
           ON A.RowOrder = B.RowOrder

edited Nov 08 '19 at 20:10

answered Nov 08 '19 at 17:50

BarneyL

1,332
8
15

1

Thanks for highlighting the issues, I've corrected but keep your answer in you deserve a vote too. – BarneyL Nov 08 '19 at 20:15

JosephDoggie · Answer 2 · 2019-11-13T13:25:18.843

There are currently two issues with the accepted answer: I) a missing comma before the line: "Table B As" II) TSQL seems to find it recursive as written, so I re-wrote it in a non-recursive way:

This is a re-working of the accepted answer that actually works in T-SQL:

USE [Database_1];

 With 
   CTE_A AS
    (
    SELECT TOP 1 [Col1] as myCol1 
    ,Row_Number() OVER (ORDER BY [Col2] desc)  AS RowOrder 
    FROM [TableA]
    )
    ,
    CTE_B AS
    (
    SELECT TOP 1 [Col2] as myCol2
    ,Row_Number() OVER (ORDER BY [Col2] desc)  AS RowOrder 
    FROM  [TableB] 
    )
    SELECT A.myCol1, B.myCol2
    FROM CTE_A AS A
    INNER JOIN CTE_B  AS B
       ON ( A.RowOrder = B.RowOrder)

TSQL -- display results of two queries on one row in SSMS

2 Answers2