--原数据
col1 col2 col3 col4 col5 col6 .......
AAA BBB CCC 1 0 1
col1 col2 col3 col4 col5 col6 .......
AAA BBB CCC 3 7 9
col1 col2 col3 col4 col5 col6 .......
AAA EEE CCC 5 3 2
col1 col2 col3 col4 col5 col6 .......
AAA BBB DDD 5 3 2--想要的结果
col1 col2 col3 col4 col5 col6 .......
AAA BBB CCC 1 0 1 3 7 9AAA EEE CCC 5 3 2AAA BBB DDD 5 3 2
要的结果就是前面三个字段值相同的只显示一行,后面字段的值也不合并就在原行里显示!
col2=(case when no=1 then col1 else '' end),
col3=(case when no=1 then col1 else '' end),col4.....
from (select no=row_number() over(partition by col1,col2,col3 order by getdate()),* from tb) t
case px when 1 then col1 else '' end as col1,
case px when 1 then col2 else '' end as col2,
case px when 1 then col3 else '' end as col3,
*
from
(select px=row_number()over(partition by col1,col2,col3 order by getdate()),* from tb)t
--想要的结果
ID col1 col2 col3 col4 col5 col6 .......
1 AAA BBB CCC 1 0 1 3 7 9 2 AAA EEE CCC 5 3 2 3 AAA BBB DDD 5 3 2
在记录前加一个序列号呢?
col2=(case when no=1 then col1 else '' end),
col3=(case when no=1 then col1 else '' end),col4.....
from (select no=row_number() over(partition by col1,col2,col3 order by getdate(),
px=rank() over(order by col1,col2,col3),* from tb) t
case px when 1 then rankid else '' end as id,
case px when 1 then col1 else '' end as col1,
case px when 1 then col2 else '' end as col2,
case px when 1 then col3 else '' end as col3,
*
from
(select
rankid=DENSE_RANK()over(partition by col1,col2,col3 order by getdate()),
px=row_number()over(partition by col1,col2,col3 order by getdate()),
*
from tb)t
1 AAA BBB CCC 1 0 12 3 7 93 AAA EEE CCC 5 3 24 AAA BBB DDD 5 3 2
id=row_number()over(order by getdate()),
case px when 1 then rankid else '' end as id,
case px when 1 then col1 else '' end as col1,
case px when 1 then col2 else '' end as col2,
case px when 1 then col3 else '' end as col3,
*
from
(select
px=row_number()over(partition by col1,col2,col3 order by getdate()),* from tb)t