CREATE TABLE A
(
    uid INT,
    pname CHAR(4),
    pcount INT,
    pmoney INT
)

CREATE TABLE B
(
    deptID INT,
    groupID INT,
    uid int    
)

INSERT a
SELECT 1,'A',1,2 UNION ALL
SELECT 2,'B',1,4 UNION ALL
SELECT 3,'A',1,2 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 2,'A',1,2 UNION ALL
SELECT 3,'B',1,4 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 1,'A',1,2 

INSERT b
SELECT 1,1,1 UNION ALL
SELECT 1,1,2 UNION ALL
SELECT 1,2,3 

结果要求：
deptid      groupid     uid         A           A   _count  B           B   _count
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           1           1           10          5           0           0
1           1           2           2           1           4           1
1           2           3           2           1           4           1


解决方案：
DECLARE @sql NVARCHAR(MAX)

SELECT @sql=ISNULL(@sql+',','')+QUOTENAME(pname)+'=sum(case when pname=N'+QUOTENAME(pname,'''')+'
then pmoney else 0 end),'+QUOTENAME(pname+'_count')+'=sum(case when pname=N'+QUOTENAME(pname,'''')+'
then pcount else 0 end)' FROM a GROUP BY pname

SET @sql='select b.deptid,b.groupid,t.* from b left join(select uid,'+@sql+' from a group by uid) t on t.uid=b.uid'

EXEC(@sql)

问题：
PIVOT能否实现？如何采用PIVOT实现？

CREATE TABLE A
(
    uid INT,
    pname varCHAR(4),
    pcount INT,
    pmoney INT
)

CREATE TABLE B
(
    deptID INT,
    groupID INT,
    uid int    
)

INSERT a
SELECT 1,'A',1,2 UNION ALL
SELECT 2,'B',1,4 UNION ALL
SELECT 3,'A',1,2 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 2,'A',1,2 UNION ALL
SELECT 3,'B',1,4 UNION ALL
SELECT 1,'A',1,2 UNION ALL
SELECT 1,'A',1,2 

INSERT b
SELECT 1,1,1 UNION ALL
SELECT 1,1,2 UNION ALL
SELECT 1,2,3 


DECLARE @sql NVARCHAR(1000), @sql1 NVARCHAR(1000),@sql2 NVARCHAR(1000)
SELECT @sql1=ISNULL(@sql1+',','')+QUOTENAME(pname) FROM a GROUP BY pname
SELECT @sql2=ISNULL(@sql2+',','')+QUOTENAME(pname+'_count') FROM a GROUP BY pname
SET @sql='select * from b m left join 
        (select * from (select uid,pname,pmoney from a) x pivot (sum(pmoney) 
          for pname in ('+@sql1+'))t) n on m.uid=n.uid
         left join (select * from (select uid,pname+''_count'' pname,pcount from a) x pivot 
          (sum(pcount) for pname in ('+@sql2+'))s) o on m.uid=o.uid'
print @sql
EXEC(@sql)

/*
deptID      groupID     uid         uid         A           B           uid         A_count     B_count
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           1           1           1           10          NULL        1           5           NULL
1           1           2           2           2           4           2           1           1
1           2           3           3           2           4           3           1           1

(3 行受影响)

------解决方案--------------------
楼主这样写就行了，如果是单列 行转列可用pivot，大于1列不推荐用pivot

参照例子
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

------解决方案--------------------
SQL code
*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数