In reports, duplicate data can be basically not displayed. How can we achieve the following in SQL?
a10a20b30b40b50
Shown:
a1020b304050
The SQL statement is as follows:
create table #a (part varchar(10),price int)goinsert into #a values('a',10)insert into #a values('a',20)insert into #a values('b',30)insert into #a values('b',40)insert into #a values('b',50)goselect * from #ago select part ,MIN(price) price into #b from #a group by part goselect * from #a select * from #bgo select case when price in (select price from #b) then part else '' end ,price from #a go
Reference: http://bbs.csdn.net/topics/310112824
Main content:
Solution 1:
If object_id ('[tab]') is not null drop table [tab] create table [tab] ([unit] varchar (6), [name] varchar (4 ), [education] varchar (4) insert [tab] select 'workshop 1 ', 'zhang san', 'bachelor degree 'Union allselect', 'Li si ', 'undergraduate course 'Union allselect' Workshop 1 ', 'wang 5', 'undergraduate course 'Union allselect' Workshop 2', 'wang zhong', 'specialist 'Union allselect' Workshop 2 ', 'Liu yi', 'specialization' select unit, name, degree from (select unit, name = (select top 1 name from tab where ta. unit = unit and ta. education level = education level), education level, s1 = 0, s2 = unit, s3 = 0 from tab ta group by unit, education level union allselect '', name,'', s1 = 0, s2 = unit, s3 = 1 from tab tb where name <> (select top 1 name from tab where tb. unit = unit and tb. education level = education level) torder by s1, s2, s3/* organization name education level ------ ---- Workshop 2 Wang Zhong specialist Liu Yi Workshop 1 Zhang San bachelor Li Si Wang Wu (affected rows are 5 rows) */
Solution 2:
Select name into # temp from (select [unit], max (name) as name from [tab] group by [unit]) Tselect case when name in (select * from # temp) then [unit] else ''end, name, case when name in (select * from # temp) then [education] else ''endfrom [tab]