Inserting horizontal row in result of query

• csn

  SSC Enthusiast

  Points: 154

  More actions

  July 2, 2008 at 6:20 am

  #193406

  hi all ,

  im joing two tables using union and displaying result , but here i want to display a horizontal line between firsttable and second table .

  can any one could explain me how to get horizontal line ...

  thanks ...

• Grant Fritchey

  SSC Guru

  Points: 399047

  More actions

  July 2, 2008 at 6:37 am

  #837168

  Several problems where. A horizontal line is formatting. That's not what TSQL is all about.

  Also, a UNION query doesn't necessarily return the rows from table 1 and then the rows from table 2. This is because a UNION also acts like a DISTINCT clause and eliminates common values between both tables. The way to avoid this is to perform a UNION ALL, but even then you may see ordering that has rows from each table mixed. The only way to determine order is to put in an ORDER BY clause. You could add an alias column like this:

  SELECT...

  '1' AS TableNumber

  FROM....

  UNION

  SELECT....

  '2' AS TableNumber

  FROM....

  ORDER BY TableNumber DESC

  That would absolutely ensure the first table values are before the second.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• csn

  SSC Enthusiast

  Points: 154

  More actions

  July 3, 2008 at 11:35 pm

  #838211

  Thanks for ur reply ,

  in my result i have to display a sum row . im tryiing to separate these sum row from data . can u give me an example for this ...

  thanks

• Grant Fritchey

  SSC Guru

  Points: 399047

  More actions

  July 7, 2008 at 6:36 am

  #838799

  It really sounds like you're trying to generate reports.

  I'd suggest one of two options, first, use a reporting tool, like the Reporting Services tool that comes with SQL Server or maybe Excel. Second, if you have to generate the reports out of TSQL, then write your select statements independently and then output as text instead of to a grid. This will allow multiple result sets to go into a single location. So you would write the basic select statement for the data you want to return and then write the aggregate statement that shows the accumulative data. Put the two select statements, one after another into the procedure and then use Management Studio to run it by hand, changing the output to text. Or you could use sqlcmd utility to run it automatically and output to text.

  This all assumes I'm understanding what you're trying to do.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning