sql 合并2行的数据 为1行

[树无影]

如下：
数据库1：
a b
1 4
2 3
3 6

变成数据库2：
a1b a2b a3b
4    3   6

怎么实现呀？求指导。

[xiaodongni]

select [1] as a1b,[2] as a2b,[3] as a3b from tablename pivot(max(b) for a in([1],[2],[3]) )as p

[还在加载中灬]

你参考一下

SELECT [1][a1b],[2][a2b],[3][a3b] FROM CTE PIVOT(SUM(b)FOR a IN([1],[2],[3]))P

[树无影]

引用 1 楼 alimake 的回复:

select [1] as a1b,[2] as a2b,[3] as a3b from tablename pivot(max(b) for a in([1],[2],[3]) )as p

a b c d
1 2 3 4
2 5 5 5 

变成 

a1 b1 c1 d1 a2 b2 c2 d2
2  3  4  5  5  5  5  5
呢

[树无影]

刚写错了，是这样的。

a b c d
1 2 3 4
2 5 5 5 

变成 

a1 b1 c1 a2 b2 c2
2  3  4  5  5  5  
呢

[还在加载中灬]

你看下，需不需要动态处理

SELECT
	MAX(CASE a WHEN 1 THEN b END) a1
	,MAX(CASE a WHEN 1 THEN c END) b1
	,MAX(CASE a WHEN 1 THEN d END) c1
	,MAX(CASE a WHEN 2 THEN b END) a2
	,MAX(CASE a WHEN 2 THEN c END) b2
	,MAX(CASE a WHEN 2 THEN d END) b2
FROM
	tablename

[树无影]

引用 5 楼 ky_min 的回复:

你看下，需不需要动态处理

SELECT
	MAX(CASE a WHEN 1 THEN b END) a1
	,MAX(CASE a WHEN 1 THEN c END) b1
	,MAX(CASE a WHEN 1 THEN d END) c1
	,MAX(CASE a WHEN 2 THEN b END) a2
	,MAX(CASE a WHEN 2 THEN c END) b2
	,MAX(CASE a WHEN 2 THEN d END) b2
FROM
	tablename

嗯 好的 谢谢啦

[xxfvba]

create table #t(a varchar(2),b varchar(10),c varchar(10), d varchar(10)) insert #t select '1','2','3','4' union all select '2','5' ,'5','5' declare @s varchar(max) select @s=isnull(@s+',','')+'MAX(case when a='''+a+''' then b end) as [a'+a+'], max(case when a='''+a+''' then c end) as [b'+a+'],max (case when a='''+a+''' then d end) as [c'+a+']' from #t group by a set @s='select '+@s+' from #t' print @s exec(@s) drop table #t