How to Add a "Custom" Row to The Top of a Select Result Set

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.

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).

Insert Row at top of the table

Unless you use a temporary table would need to repeat ID

select Store_Desc + ' (' + Store_ID +')' as storename, ID  
from table_name  
union all 
select 'fruit', '0'
order BY  ID  

or

declare @T table (storename varchar(20), ID varchar(10));
insert into @T values ('fruit', '0');
insert into @T 
select select Store_Desc + ' (' + Store_ID + ')', ID  
from  table_name;
select storename 
from @T 
order by ID;

Add a row at the end of SQL Select using UNION

Change the last UNION to a UNION ALL to skip elimination of duplicates (which as a side effect orders the lines). That should be enough...

...
FROM PS_PO_GROUP_TBL 
UNION ALL
SELECT 'NON_IMMO' 
...

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.

Oracle SQL: Adding row to select result

As @Serpiton suggested, it seems the functionality you're really looking for is the ability to add sub-totals to your result set, which indicates that rollup is what you need. The usage would be something like this:

SELECT   id,
         group_id,
         coalesce(type, 'budget total') as type,
         sum(val) as val
FROM     your_table
GROUP BY ROLLUP (group_id), id, type

Append text to each row of the sql select query

You can easily concatenate the string using the following. You will use the + to concatenate the string to the type column and the count. Note, the count needs to be converted to a varchar for this to work:

SELECT 
  'The number of records affected for '+ type +
    ' is : '+ cast(COUNT(ID) as varchar(50)) as'Records Affected'
FROM yt
GROUP BY TYPE;

See SQL Fiddle with Demo

---

Related Topics