Add Row to Query Result Using Select

How can I add a custom row to the top of a select result set?

This is the safe way to do this:

select name, email
from ((select 'name' as name, 'email' as email, 1 as which
      ) union all
      (select name, email, 2 as which from [dbo].[testTable]
      )
     ) t
order by which;

In practice, union all will work:

      select 'name' as name, 'email' as email
      union all
      select name, email from [dbo].[testTable]

However, I cannot find documentation that guarantees that the first subquery is completed before the second. The underlying operator in SQL Server does have this behavior (or at least it did in SQL Server 2008 when I last investigated it).

Add row in result set of Select Query

If you're just looking for a summary of rows by name, you could use a union, and a group by, and an order by:

DECLARE @tbl TABLE (Name char(3), Amount int)

insert @tbl
VALUES 
 ('AAA',15)
,('AAA',20)
,('CCC',30)
,('CCC',50)

select Name, Amount
FROM @tbl

UNION ALL

SELECT Name, SUM(Amount)
FROM @tbl GROUP BY Name

ORDER BY Name

For a working example using subquery and CTE (only used as an example replacement for the actual table), take the following:

with vals as
(
    select 'aaa' as Name, 15 as Amount
    union all
    select 'aaa' as Name, 10 as Amount
    union all
    select 'bbb' as Name, 20 as Amount
    union all
    select 'bbb' as Name, 30 as Amount
    union all
    select 'bbb' as Name, 50 as Amount
)
select *
from
(
    select 'Amount' as ValType, Name, Amount
    from vals
    union all
    select 'Total' as ValType, Name, sum(Amount)
    from vals
    group by Name
)
order by Name, ValType
;

This will group by the Name column, and sum within a subquery, then sort the output using manually added ValType identifier column.

SQL - Add Extra Row to Results Set

As others in the comments have noted, it makes more sense for this kind of logic to be handled by the reporting solution itself, even if that software is something as simple as excel.

However, if I understand your requirement correctly, you could use a UNION of two SELECT queries;

Something like;

SELECT ProductID, ProductName, ProductCat, Price, AmountSold
FROM YourTable

UNION ALL

SELECT 0, 'Sum of 1 & 2', 0, SUM(Price), SUM(AmountSold)
FROM YourTable
WHERE (ProductID = 1 OR ProductID = 2);

This assumes that your sums, etc will match or be convertable to the data types of the original columns. The use of UNION ALL performs better and prevents your new row from being removed in the unlikely event that all it's columns values match an existing row.

I hope this helps.

Manually add records to the result of a SQL select query via stored procedure call using SQL Server

You can use union, as in:

SELECT [Location] 
FROM Emp
WHERE [Location] IS NOT NULL
UNION  -- on purpose to remove duplicates
SELECT Location
FROM (VALUES ('Maine'), ('Florida')) v(Location);

That said, I don't see a good reason to make this a stored procedure. You should make it either a view or a user-defined table function. That say, the code can be used from inside a SELECT query.

Add row to Firebird query result using select

from clause is mandatory in Firebird, so write from rdb$database.

rdb$database is a system table that has only a single row.

How to dynamically add row(s) in SQL query result

The basic structure is to do this with a cross join and left join. Your query is a bit complicated, so I would suggest:

with t as (
      SELECT T.TestTypeId, F.FlockCode, AMT, CV, S.SampleDate 
      FROM [dbo].[Tests] T JOIN
           [Samples] S
           ON T.SampleId = S.Id JOIN
           [TestTypes] TT
           ON T.TestTypeId = TT.Id  JOIN
           [Flocks] F
           ON T.FlockId = F.Id 
      WHERE T.TestTypeId = 7 AND T.IsDeleted = 0 AND
            S.SampleDate BETWEEN '2017-12-17' AND '2018-01-08' AND
            T.FlockId IN (7, 13)
     )
SELECT f.TestTypeId, f.FlockCode, COALESCE(t.AMT, 0) as AMT,
       COALESCE(t.CV, 0) as CV, d.SampleDate
FROM (SELECT DISTINCT TestTypeId, FlockCode FROM t) f CROSS JOIN
     (SELECT DISTINCT SampleDate FROM t) d LEFT JOIN
     t
     ON f.FlockCode = t.FlockCode AND f.TestTypeId = t.TestTypeId,
        d.SampleDate = t.SampleDate
ORDER BY d.SampleDate, f.FlockCode;

How to append rows to result from SELECT query in SQL?

UNION with a calculated column or UNION ALL should work.

SELECT
    *
FROM
(
    SELECT
        OrderID = 1 
        RXID, 
        DrName,
        SBOID,
        SBOName,
        RxHonoredDate,
        RxnHonoured,
        CallRecievedFrom,
        MobileNo  
    FROM 
        Query Q

    UNION

    SELECT 
        OrderID = 2
        RXID = NULL, 
        DrName = DrID, 
        SBOID = NULL, 
        SBOName = SBOID, 
        RxHonoredDate = NULL,  
        RxnHonoured, 
        CallRecievedFrom, 
        MobileNo = Mobile 
    FROM 
        SampleRequest) AS X
ORDER BY
    OrderID

Add a row number to result set of a SQL query

The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A is the single-column clustering key for t:

SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
  FROM dbo.tableZ AS t
  ORDER BY t.A;

If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:

ROW_NUMBER() OVER (ORDER BY @@SPID)

-- or for serial plans

ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)

Little tricks I picked up from Paul White in this article (see "Paul's Solution").

Not sure what the variables in your question are supposed to represent (they don't match).

---

Related Topics