Insert blank row between groups of rows and sorted by ID in sql

I have a table which has the following columns and values

ID       TYPE     NAME
1      MAJOR      RAM
2      MAJOR  开发者_如何学编程    SHYAM
3      MAJOR      BHOLE
4      MAJOR      NATHA
5      MINOR      JOHN
6      MINOR      SMITH

My requirement is to right a stored procedure (or SQL query) which would return the same resultset except that there will be blank line after the TYPE changes from one type to another type (major, minor).

MAJOR      RAM
MAJOR      SHYAM
MAJOR      BHOLE
MAJOR      NATHA

MINOR      JOHN
MINOR      SMITH

While i use this query for adding blank line but it is not sorted by basis of ID

select TYPE, NAME from (
select
TYPE as P1,
1 as P2,
ID,
TYPE,
NAME
from EMP
union all
select distinct
TYPE,
2,
'',
'',
N''
from EMP
) Report
order by P1, P2
go

How i sort data by ID

Thanks in advance

Yes, yes, don't do this, but here's the query to do it, assuming SQL Server 2008 R2. Other versions/rdbms you can achieve same functionality by writing two separate queries unioned together.

Query

; WITH DEMO (id, [type], [name]) AS
(
SELECT 1,'MAJOR','RAM'
UNION ALL SELECT 2,'MAJOR','SHYAM'
UNION ALL SELECT 3,'MAJOR','BHOLE'
UNION ALL SELECT 4,'MAJOR','NATHA'
UNION ALL SELECT 5,'MINOR','JOHN'
UNION ALL SELECT 6,'MINOR','SMITH'   
)
, GROUPED AS
(
    SELECT
        D.[type]
    ,   D.[name]
    ,   ROW_NUMBER() OVER (ORDER BY D.[type] ASC, D.[name] DESC) AS order_key
    FROM
        DEMO D
    GROUP BY
    --grouping sets introduced with SQL Server 2008 R2
    -- http://msdn.microsoft.com/en-us/library/bb510427.aspx
    GROUPING SETS
    (
        [type]
    ,   ([type], [name])
    )
)
SELECT
    CASE WHEN G.[name] IS NULL THEN NULL ELSE G.[type] END AS [type]
,   G.[name]
FROM 
    GROUPED G
ORDER BY
    G.order_key

Results

If you don't like the nulls, use coalsece to make empty strings

type    name
MAJOR   SHYAM
MAJOR   RAM
MAJOR   NATHA
MAJOR   BHOLE
NULL    NULL
MINOR   SMITH
MINOR   JOHN
NULL    NULL

I agree with billinkc. In a sequential mind, like mine, it can occur different. The approach is to use a cursor and insert the records into a temp table. This table can have a column, INT type, lets say it is called "POSITION" which increments with every insert. Check for ID changes, and add the empty row everytime it does. Finally make the SELECT order by "POSITION".

My context was: An interface that dinamically adjust to what the user needs, one of the screens shows a payment table, grouped by provider with the approach early mentioned. I decided to manage this from database and skip maintainance for the screen at client side because every provider has different payment terms.

Hope this helps, and lets keep an open mind, avoid saying "don't do this" or "this is not what SQL was designed for"