行列转换,Excel行列如何转换

问题:Excel行列怎么样转移?

① 、MSSQL二〇〇五事先的版本行列转换case
when+(sum or max)那类聚合函数

CREATE proc [dbo].[R_Parities_Config_CurrencyExchangeRate_V2]
    @FromCode nvarchar(400),
    @ToCode nvarchar(400),
    @EffectiveDate nvarchar(400),
    @ChangeDate nvarchar(400)
    as
    begin
        SET NOCOUNT ON;
        select f.col as FromCode into #FromTable from (select distinct
col from dbo.[行列转换,Excel行列如何转换。Split](@FromCode, ‘,’)) as f
            where f.col in (select distinct FromCode from
Config_CurrencyExchangeRate where ToCode = @ToCode and FromCode !=
@ToCode)
        select * into #ToTable from Config_CurrencyExchangeRate
            where ToCode = @ToCode and EffectiveDate >=@EffectiveDate
and EffectiveDate <=@ChangeDate
        
        select a.EffectiveDate,c.FromCode+’/’+a.ToCode as
CurrencyType,a.Rate into #a from #ToTable as a
        right join #FromTable as c on c.FromCode = a.FromCode order by
a.EffectiveDate desc
 
    declare @groupField varchar(1000)
  select @groupField=isnull(@groupField,”)+case when
isnull(@groupField,”)=” then ” else ‘,’ end+QUOTENAME(CurrencyType)
  from (select rtrim(CurrencyType) as CurrencyType from (select distinct
CurrencyType from #a) as a )t  
  declare @sql nvarchar(4000)
  set @sql=N’
  select *
  from
        (select  EffectiveDate,rtrim(CurrencyType) as CurrencyType,
   MAX(Rate) as Rate  
        from #a
        group by EffectiveDate,rtrim(CurrencyType)
        ) as x
   pivot (MAX(Rate)for CurrencyType in (‘+@groupField+’)) as pvt
   order by EffectiveDate desc’
  EXEC (@sql)
 
  drop table #a

透过点击行列的图标进行转换,以下为意义图片及代码。

回答:在Excel表中大家平时会遇到把行转为列,把列转为行。当然相信还有众多少人都以二个一个去把行的剧情复制粘贴到列,那样做重新了很频仍还要工效相当的低。那么有何样措施能够飞速把Excel表的行转为列,列转为行吧?那里给我们介绍三个办法,保障你看完二遍就懂!

/*MSSQL2005版本之前行列转换*/
;with ScoreTab(name,subject,score) as
(
 select 'Tom','Chinese',80
 union all
 select 'Frank','Chinese',90
 union all
 select 'Jerry','Chinese',70
 union all
 select 'Tom','English',70
 union all
 select 'Frank','English',80
 union all
 select 'Jerry','English',90
)
测试表数据:

/*每一行做一个判断,为Chinese就不可能是English所以补0*/
select name
   ,case subject when 'Chinese' then score else 0 end as Chinese
   ,case subject when 'English' then score else 0 end as English
 from ScoreTab
--未聚合之前行列转换后的数据:

 --演变聚合sum,实现行转列 这里用max进行聚合也是一样的,因为除了0也就一个(仅限于一个人对应科目
--数只有一次记录不会出现Tom 两次英语成绩这样的记录)
/*
select name
   ,sum(case subject when 'Chinese' then score else 0 end) as Chinese
   ,sum(case subject when 'English' then score else 0 end) as English
   from ScoreTab
   Group by name
*/
聚合之后真正实现行转列的数据:

    end
 
 –execute [R_Parities_Config_CurrencyExchangeRate_V2]
‘CAD,USD’,’USD’,’2010-12-12′,’2016-12-12′
 
 –select distinct ToCode,FromCode from Config_CurrencyExchangeRate
GO

图片 1图片 2

点击文章右上角给作者二个“关心”鼓励,多谢你!

 

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>无标题文档</title>
<style type="text/css">
    *{
                    margin:0px;
                    padding:0px;
                }
    body{
                        font:"微软雅黑";
                        font-size:14px;
                    }
    a{
                      text-decoration:none;
                 }
    ul,li{
                        list-style:none;
                    }
    #box{
                        height:auto;
                        width:550px;
                        border:1px solid #aaa;
                        margin:0 auto;
                        overflow:hidden;
                    }
    .top{
                        height:40px;
                    }
    .top a{
                        height:16px; 
                        width:16px;
                        display:block;
                        float:right;
                        margin:5px;
                    }
    .btn-list-off{
                                    background:#fff url('.tns.jpg') no-repeat /*@如何定位背景图片@-38px 0px*/-38px 0px;
                                }
            .btn-car-on{
                                background:#fff url('.tns.jpg') no-repeat -21px -34px;
                              }
    .btn-list-on{
                                 background:#fff url('.tns.jpg') no-repeat -21px 0px;
                             }
    .btn-car-off{
                                        background:#fff url('.tns.jpg') no-repeat -38px -32px;
                                }

    #box ul li{
                                height:auto;width:164px;
                                border:1px solid #aaa;/*@问题?如何让每个@float:left*/
                                float:left;
                                margin:7px;
                            } 
    .a-img{
                            height:164px;
                            width:164px;
                            display:block;
                            overflow:hidden;/*问题overflow:hide,如何隐藏超出部分的图片?overflow:hidden;还有其他什么左右?*/
                            }
    p a,p span{
        display:block;
        line-height:23px;
        padding-left:10px;
    }
    .bottom{
                            height:40px;
                            line-height:40px;
                            text-align:center;
                            background-color:#ccc;
                        }
    .small{
                            height:50px;
                            width:50px;
                            /*问题@float:left;如何让文字从图片下方至图片的右侧呢@*/
                            float:left;
                            margin:5px;
                        }
                        img{
                            width: 160px;
                            height: 180px;
                        }
</style>
<script type="text/javascript">
    window.onload =function(){   //@@问题:window.onload 的作用是什么?
        //获取按钮
        var listBtn = document.getElementById("btn1");   
        var carBtn = document.getElementById("btn2");
        var imgs = document.getElementsByTagName("img");//@@问题:如何获取多个DOM元素?

        listBtn.onclick = function(){
            //改变按钮的class的值                     //@@问题:如何改变一个元素的样式?
            listBtn.className  = "btn-list-on";
            carBtn.className = "btn-car-off";
            //改变每一个图片的路径以及它的对应的父节点的class的值 
            for(var i=0;i<imgs.length;i++)
            {
                imgs[i].src = ".small.jpg";             //@@问题,怎么更换一个图片呢?
                imgs[i].parentNode.className = "a-img small"; //@@问题,怎么获取一个元素的父节点?
            }
        }
        carBtn.onclick = function(){
            //改变按钮的class
            listBtn.className  = "btn-list-off";
            carBtn.className = "btn-car-on";
            //改变每一个图片的路径以及它的对应的父节点的class的值
            for(var i=0;i<imgs.length;i++)
            {
                imgs[i].src = ".ig.jpg";
                imgs[i].parentNode.className = "a-img";
            }
        }   

    }

</script>
</head>
<body>
<div id="box">
    <div class="top">
        <a href="#" title="列表模式" id="btn1" class="btn-list-off"></a>
        <a href="#" title="卡片模式" id="btn2" class="btn-car-on"></a>
    </div>
    <ul>
        <li>
            <div class="con">
            <a href="#" class="a-img ">
                <img src="images/big.jpg" />
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li>

         <li>
            <div class="con">
            <a href="#" class="a-img">
                <img src="images/big.jpg"/>
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li><li>
            <div class="con">
            <a href="#" class="a-img">
                <img src="images/big.jpg"/>
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li>
        <li>
            <div class="con">
            <a href="#" class="a-img">
                <img src="images/big.jpg"/>
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li><li>
            <div class="con">
            <a href="#" class="a-img">
                <img src="images/big.jpg"/>
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li><li>
            <div class="con">
            <a href="#" class="a-img">
                <img src="images/big.jpg"/>
            </a>
            <p>
                <a href="#">白阳</a>
                辽宁
                21个共同好友
             </p> 
             </div>
             <div class="bottom">
                未分组的好友
             </div>
        </li>
    </ul>
</div>
</body>
</html>

大家先来对待下边两张图纸:

图片 3

  

当下边表格的名字愈来愈多时,表格也就越是来,查看也就特别麻烦。图片 4