当前位置：首页 → 问答吧 → ==========行转列(PIVOT函数求解)=========

==========行转列(PIVOT函数求解)=========

时间：2011-12-03

来源：互联网

近日在处理行转列时遇到困惑，特向C友们求解。
测试数据如下：
SQL code


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实现？