求助一个行转列的问题。

大腹 2012-10-09 04:38:32

数据如下：
Organization Province P93 P97
社会单位 101 7.03 7.66
中石化 101 7.08 7.67
中石油 101 7.11 7.67
社会单位 102 7.03 7.66
中石化 102 7.08 7.67
中石油 102 7.11 7.67

经行转列，数据变化成：
Province 社会单位P93 中石化P93 中石油P93 社会单位P97 中石化P97 中石油P97
101 7.03 7.08 7.11 7.66 7.67 7.67
102 7.03 7.08 7.11 7.66 7.67 7.67

...全文

143 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

-Tracy-McGrady- 2012-10-09

打赏
举报



--测试数据2

create table testTable(Organization nvarchar(10),Province varchar(5),P93 float,P97 float)

insert into testTable

select '社会单位','101',7.03,7.66 union all

select '中石化','101',7.08,7.67 union all

select '中石油','101',7.11,7.67 union all

select '社会单位','102',7.03,7.66 union all

select '中石化','102',7.08,7.67 union all

select '中石油','102',7.11,7.67

--方法二,动态SQL

declare @sqlStr varchar(8000) 

set @sqlStr = 'select Province ' 

select @sqlStr = @sqlStr + ' , max(case Organization when ''' + Organization + ''' then P93 else 0 end) [' + Organization +'P93'+ ']' 

from (select distinct Organization from testTable) as a 

select @sqlStr = @sqlStr + ' , max(case Organization when ''' + Organization + ''' then P97 else 0 end) [' + Organization +'P97'+ ']'

from (select distinct Organization from testTable) as a 

set @sqlStr = @sqlStr + ' from testTable group by Province' 

exec(@sqlStr)

--结果2

Province 社会单位P93                中石化P93                 中石油P93                 社会单位P97                中石化P97                 中石油P97

-------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------

101      7.03                   7.08                   7.11                   7.66                   7.67                   7.67

102      7.03                   7.08                   7.11                   7.66                   7.67                   7.67



(2 行受影响)

gongjian0628 2012-10-09

打赏
举报

http://blog.csdn.net/gongjian0628/article/details/7878066

-Tracy-McGrady- 2012-10-09

打赏
举报



--测试数据

declare @table table(Organization nvarchar(10),Province varchar(5),P93 float,P97 float)

insert into @table

select '社会单位','101',7.03,7.66 union all

select '中石化','101',7.08,7.67 union all

select '中石油','101',7.11,7.67 union all

select '社会单位','102',7.03,7.66 union all

select '中石化','102',7.08,7.67 union all

select '中石油','102',7.11,7.67

--SQL语句

select Province,

max(case Organization when '社会单位' then P93 else 0 end) 社会单位P93,

max(case Organization when '中石化' then P93 else 0 end) 中石化P93,

max(case Organization when '中石油' then P93 else 0 end) 中石油P93,

max(case Organization when '社会单位' then P97 else 0 end) 中石化P97,

max(case Organization when '中石化' then P97 else 0 end) 中石化P97,

max(case Organization when '中石油' then P97 else 0 end) 中石化P97

from @table group by Province

--结果

Province 社会单位P93                中石化P93                 中石油P93                 中石化P97                 中石化P97                 中石化P97

-------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------

101      7.03                   7.08                   7.11                   7.66                   7.67                   7.67

102      7.03                   7.08                   7.11                   7.66                   7.67                   7.67



(2 行受影响)