一行数据如何变两行

[不穿内裤的超人]

in_seq use_dept
20144570 004
20144570 027
20144598 004
20144598 031

想要的结果
in_seq use_dept1 use_dept2
20144570 004 027
20144598 004 031

use_dept 必定有两个，不多也不少

[sql2015]

with a(in_seq,use_dept) as(
select '20144570','004' union
select '20144570','027' union
select '20144598','004' union
select '20144598','031'
)
select in_seq,[1] as use_dept1,[2] as use_dept2 from 
(select *,rn=ROW_NUMBER() over(partition by in_seq order by use_dept) From a ) b
pivot(max(use_dept) for rn in ([1],[2])) c

[qq_29702469]

with biaoge(in_seq,use_dept) as( select '20144570','004' unionselect '20144570','027' unionselect '20144598','004' unionselect '20144598','031') select cin_seq, use_dept=stuff((select ', 'use_dept' from biaoge t where cin_seq=tb.cin_seq for xml path('')), 1, 1, '') from biaoge tb group by cin_seq,use_dept

[xdashewan]

搜索sql行列转换

[早起晚睡]

use_dept这个值有什么特点吗？？

[道素]

with a(n_seq,use_dept) as(
select '20144570','004' union
select '20144570','027' union
select '20144598','004' union
select '20144598','031'
)
SELECT t.n_seq,MAX(CASE WHEN t.seq=1 THEN t.use_dept ELSE '' END) AS use_dept1 
        ,MAX(CASE WHEN t.seq=2 THEN t.use_dept ELSE '' END) AS use_dept2
FROM (
select *,row_number()over( PARTITION BY n_seq ORDER BY use_dept) AS seq from a
) t GROUP BY t.n_seq

[Ginnnnnnnn]

SELECT in_seq,MIN(use_dept) AS use_dept1,MAX(use_dept) AS use_dept2
	FROM tb 
		GROUP BY in_seq

[xxfvba]

With T1 (in_seq,use_dept) as (select '20144570','004' union all select '20144570','027' union all select '20144598','004' union all select '20144598', '031') select *,ROW_NUMBER() over (partition by in_seq order by use_dept) as rn into #T from T1 select * from #T declare @s varchar(max) select @s=ISNULL(@s+',','')+'MAX(case when rn='''+convert(varchar,rn)+''' then use_dept end) as [use_dept'+convert(varchar,rn)+']' from #T group by rn set @s='select in_seq,'+@s+' from #T group by in_seq' print @s exec(@s) drop table #T

[早起晚睡]

引用 4 楼 KanzakiOrange 的回复:

SELECT in_seq,MIN(use_dept) AS use_dept1,MAX(use_dept) AS use_dept2
	FROM tb 
		GROUP BY in_seq

这个好简单

[xiaoxiangqing]

先用row_number分组排序，就用union all显示就行了