Create Distinct Column Values as Extra Rows

Any tricks anyone can share on how to manipulate the following table

ID TYPE Name Description
1  X    A    DESC_A
2  X    B    DESC_开发者_Go百科B
3  Z    C    DESC_C

to this view?

NAME_X DESCRIPTION_X
A      DESC_A
B      DESC_B
NAME_Z DESCRIPTION_Z
C      DESC_C 

For every distinct column, I would like to create a custom row for every distinct value in the 'TYPE' column. In this example, the custom row is created by appending the TYPE value to 'NAME_' and 'DESCRIPTION_'.

Thanks!

Try this:

create view vwTestDistinctData
as
    select [type], [Description]
    from testdistinctdata
    union all
    select 
        'NAME_' + [type] as [Type],
        'DESCRIPTION_' + [type] as [Description]
    from testdistinctdata
    group by [type]
go

Edit: Return some meta data from the view:

alter view dbo.yourView
as
with c_Distinct([type])
as  (   select  distinct [Type]
        from    dbo.yourTable   
    )
select  [Sort] = 0,
        [Type],
        Name, 
        [Description] 
from    dbo.yourTable
union all
select  [Sort] = 1,
        [Type],
        'NAME_'+[Type],
        'DESCRIPTION_'+[Type]
from    c_Distinct  

And then perform the ordering when selecting from the View:

select *
from yourView
order by [Type] asc, [Sort] desc