How to write a SQL statement which can hide Duplicate values during query output

hi,

a. not related sqlserver reporting services.

i writing complex join query bring following output.

enquiryno, clientname, state, city,        orderno, orderdate, itemname,  qty.
1 ,                xxxx ,        mh,  mumbai,       1 ,       1/1/2000,       item1,     2
1 ,                xxxx ,        mh,  mumbai,       1 ,       1/1/2000,       item2,     5
22 ,              xxxx ,        mh,  mumbai,       2 ,       1/1/2001,       item6,     15
22 ,              xxxx ,        mh,  mumbai,       2 ,       1/1/2001,       item7,     20
30 ,              yyyy ,        mp,  bhopal,         45 ,     1/1/2005,       item10,   2
30,               yyyy ,        mp,  bhopal,         45 ,     1/1/2005,       item11,   1
30,               yyyy ,        mh,  pune,            46 ,     1/1/2005,       item11,   1

want modify sql query such that can output below.

enquiryno, clientname, state, city,        orderno, orderdate, itemname,  qty.
1 ,                xxxx ,        mh,  mumbai,       1 ,       1/1/2000,       item1,     2
                            ,                                     ,                    ,       item2,     5
22 ,              xxxx ,        mh,  mumbai,       2 ,       1/1/2001,       item6,     15
                                                                                       ,       item7,     20
30 ,              yyyy ,        mp,  bhopal,         45 ,     1/1/2005,       item10,   2
                                                                                       ,       item11,   1
                           ,        mh,  pune,            46 ,     1/1/2005,       item11,   1

possible ?      if not simple sql query can done stored procedure  or  commontable expression ?    how ?

regards
jiya.

 

 

 

i don't understand why last record should show state, city etc details. try following query.

  create table #temp (enquiryno nvarchar(100), clientname nvarchar(100), state nvarchar(100),  city nvarchar(100), orderno nvarchar(100), orderdate nvarchar(100), itemname nvarchar(100), qty nvarchar(100))    insert into #temp  select '1' , 'xxxx', 'mh', 'mumbai', '1' ,'1/1/2000', 'item1','2'  union all select '1' , 'xxxx' , 'mh', 'mumbai', '1' , '1/1/2000',    'item2','5'  union all select '22' , 'xxxx' , 'mh', 'mumbai', '2' , '1/1/2001','item6','15'  union all select '22' , 'xxxx' , 'mh', 'mumbai', '2' ,    '1/1/2001',    'item7',   '20'  union all select '30' , 'yyyy' , 'mp', 'bhopal', '45' ,   '1/1/2005',    'item10',  '2'  union all select '30' , 'yyyy' , 'mp', 'bhopal', '45' ,   '1/1/2005',    'item11',  '1'  union all select '30', 'yyyy' , 'mh', 'pune',   '46' ,   '1/1/2005',    'item11',  '1'    select * from #temp    select  	case when rr = 1 then enquiryno else '' end enquiryno,  	case when rr = 1 then clientname else '' end clientname,  	case when rr = 1 then state else '' end state,  	case when rr = 1 then city else '' end city,  	case when rr = 1 then orderno else '' end orderno,  	case when rr = 1 then orderdate else '' end orderdate,  	itemname,  	qty  from  	(select  	(row_number() over(partition by enquiryno order by enquiryno, clientname)) [rr], *  	from #temp) subquery1    drop table #temp  

SQL Server  >  Transact-SQL