sql server 行转列 ，求助

[飞过海啊lsq]

insert into hdinfo (name,num1,num2,num3)
select 'A',1,2,3 union
select 'B',4,5,6

select * from hdinfo ;

目前查询结果
A 1 2 3
B 4 5 6

最终想实现结果
A B
1 4
2 5
3 6

[Yole]

--> 测试数据：hdinfo
if object_id('hdinfo') is not null drop table hdinfo
go 
create table hdinfo([name] varchar(2),num1 int,num2 int,num3 int)

insert into hdinfo (name,num1,num2,num3)
 select 'A',1,2,3 union 
 select 'B',4,5,6
 
 select * from hdinfo 

select sum(case when [name] ='A' then num1 else 0 end) as A ,sum(case when [name] ='B' then num1 else 0 end) as B
from hdinfo
union all 
select sum(case when [name] ='A' then num2 else 0 end) as A ,sum(case when [name] ='B' then num2 else 0 end) as B
from hdinfo
union all
select sum(case when [name] ='A' then num3 else 0 end) as A ,sum(case when [name] ='B' then num3 else 0 end) as B
from hdinfo

/*
A           B
----------- -----------
1           4
2           5
3           6

(3 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-03-12 16:02:33
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[hdinfo]
if object_id('[hdinfo]') is not null drop table [hdinfo]
go 
create table [hdinfo]([name] varchar(1),[num1] int,[num2] int,[num3] int)
insert [hdinfo]
select 'A',1,2,3 union all
select 'B',4,5,6
--------------开始查询--------------------------
SELECT A,B FROM 
(
  SELECT * FROM 
     (SELECT name,num1,num2,num3 FROM hdinfo) p
  UNPIVOT
     (金额 FOR col IN (num1,num2,num3))AS unpvt
) T
PIVOT
(MAX(金额)  FOR name in ([A],[B]))AS pt

----------------结果----------------------------
/* A           B
----------- -----------
1           4
2           5
3           6

(3 行受影响)
*/

[BiChangQing]

都是高手，收藏学习.