对数据进行转置（从列到行）

相关文章推荐

健身的罐头 · maven突然报大量package ...· 7 月前 ·

奔跑的大脸猫 · 运行时如何定位程序集 - .NET ...· 1 年前 ·

气势凌人的可乐 · mysql中union ...· 1 年前 ·

爱逃课的骆驼 · 计算凝聚态物理学习笔记 - 知乎· 1 年前 ·

大力的椰子 · 将 PL/pgSQL 输出从 ...· 1 年前 ·

</noscript><div id="app" class="wrapper"><header id="tableau-help-article-header" class="container--full-width quick-help-header"><div class="container--centered"><div class="header__back-button"><back-button title="Go back"/></div><div class="header__mobile-menu quick-help-hidden"><menu-tree-toggle/></div><div class="header__logo quick-help-hidden"><a href="https://www.tableau.com/zh-cn/"><img src="./Resources/tableau-logo.png" class="header__logo__img" alt="Tableau"/></a></div><div class="header__search"><search-header-help placeholder="搜索"/></div><div class="header__home-button"><home-button title="Go home"/></div></div></header><div class="container--navigation-top quick-help-hidden content-only-hidden"><div id="help-subheader" class="subheader print-hidden"><div class="container--centered"><h4 class="heading--subheader">Tableau Desktop 和 Web 制作帮助</h4></div></div><div class="container--top-links"><div class="container--centered container--breadcrumbs"><div><breadcrumb-links-help/></div></div><div id="help-container-menu-headings" class="container--menu-headings"><nav class="nav-medium-screen"><menu-heading-links-static-help menu-title="本文内容" :disabled="false" :headings="pageHeadings"/></nav></div></div></div><div class="section--main container--full-width"><div class="container--centered"><nav class="nav-side nav-side--left" role="navigation"><menu-tree-help menu-title="内容"/></nav><article role="main"><h1 id="contentH1"/><div class="caption article__tags content-only-hidden quick-help-hidden"><span class="article__tags--applies-to">适用于： Tableau Cloud, Tableau Desktop, Tableau Server</span><br/><span class="article__tags--role"> </span></div><div id="content-body">&#13; <div role="main" id="mc-main-content"><p>有时，在 Tableau 中分析以交叉表格式存储的数据可能很困难。在处理 Microsoft Excel、文本文件、Google Sheets 和 .pdf 数据源时，您可以对数据进行透视，从交叉表格式转换为分列格式。如果使用其他数据源，您可以<a href="#Pivot" class="MCXref xref">使用自定义 SQL 进行透视 (Tableau Desktop)</a>。</p><p>例如，假设在三个单独的字段中有按季度针对三个供应商销售的设备数量。您可以对数据进行透视，以便供应商位于一个字段中，销售的设备数量位于另一个字段中。</p><p><img src="Img/qs_pivot_example.png" alt=""/></p><p/><h2 class="quickstart" is="heading-item" :level="2" id="对数据进行透视">对数据进行透视</h2><p>设置数据源之后，在网格中选择两个或更多列。单击列名称旁边的下拉箭头，然后选择<span class="uicontrol">“行转置为列”</span>。将创建名为“行转置为列字段名称”和行转置为列字段值”的新列并将其添加到数据源。新列将替换您在创建行转置为列时选择的原始列。</p><p><img src="Img/qs_pivot_command.png" alt=""/></p><h2 class="quickstart" is="heading-item" :level="2" id="添加到行转置为列">添加到行转置为列</h2><p>若要将更多数据添加到行转置为列，请选择另一个列，单击列名称旁边的下拉箭头，然后选择<span class="uicontrol">“将数据添加到行转置为列”</span>。在开始分析之前，请确保行转置为列列和值按预期方式显示。</p><p><img src="Img/qs_pivot_add.png" alt=""/></p><p> </p><span class="caption"> 若要移除行转置为列，请单击行转置为列列名称旁边的下拉箭头，然后选择<span class="uicontrol">“移除行转置为列”</span>。</span><h2 is="heading-item" :level="2" id="行转置为列疑难解答">行转置为列疑难解答</h2><ul><li value="1"><p><b>视图中的红色字段以及“数据”窗格中带有感叹号的字段</b>：由于原始字段已替换为新的行转置为列字段，因此对视图中原始字段的任何引用将不再起作用。它们会致字段在视图中变为红色，或在<span class="uicontrol">“数据”</span>窗格中的字段旁边显示红色感叹号。</p></li><li value="2"><p><b>网格中的空值：</b>举例来说，如果在数据提取刷新中移除了行转置为列中使用的所有原始字段，则行转置为列字段中会显示空值。</p></li><li value="3"><p><b>无行转置为列选项：</b>当您在单一 Microsoft Excel、文本文件、Google Sheets 和 .pdf 数据源中选择两个或更多列时，将出现行转置为列。如果在 Tableau Desktop 中使用其他数据源，您可以使用自定义 SQL 进行透视。</p></li></ul><h2 is="heading-item" :level="2" id="使用自定义-sql-进行透视-tableau-desktop"><a name="Pivot"/>使用自定义 SQL 进行透视 (Tableau Desktop)</h2><p>即使未使用 Excel、文本文件、Google Sheets 和 .pdf 数据源，您也可以使用自定义 SQL 对数据进行透视。在自定义 SQL 查询中使用 UNION ALL 运算符时，可以获取不同列中的值并将它们放到新列中。</p><p>例如，假设您有一个名为 <b>Contest</b> 的表。</p><p><b>Contest</b></p><table style="width: 80%;mc-table-style: url('Resources/TableStyles/Basic-Border-Light.css');" class="TableStyle-Basic-Border" cellspacing="0"><colgroup><col class="TableStyle-Basic-Border-Column-Column1"/><col class="TableStyle-Basic-Border-Column-Column1"/><col class="TableStyle-Basic-Border-Column-Column1"/></colgroup><thead><tr class="TableStyle-Basic-Border-Head-Header1"><th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Runner</th><th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Start Time</th><th class="TableStyle-Basic-Border-HeadD-Column1-Header1">End Time</th></tr></thead><tbody><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Amanda</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">9/3/2016 3:04 PM</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:25 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Oscar</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">9/3/2016 3:04 PM</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:21 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyB-Column1-Body1">William</td><td class="TableStyle-Basic-Border-BodyB-Column1-Body1">9/3/2016 3:04 PM</td><td class="TableStyle-Basic-Border-BodyA-Column1-Body1">9/3/2016 3:16 PM</td></tr></tbody></table><p>若要在 Tableau 中优化此数据的分析，您可以使用下面的自定义 SQL 查询，对“Start Time”和“End Time”列进行透视，使其值在单个列中。</p><pre xml:space="preserve">Select [Runner]</pre><pre xml:space="preserve">, 'Start' as [Action]</pre><pre xml:space="preserve">, [Start Time] as [Time]</pre><pre xml:space="preserve">From [Contest]</pre><pre xml:space="preserve">Union ALL</pre><pre xml:space="preserve">Select [Runner]</pre><pre xml:space="preserve">, 'End' as [Action]</pre><pre xml:space="preserve">, [End Time] as [Time]</pre><pre xml:space="preserve">From [Contest]</pre><p>上述自定义 SQL 查询执行以下操作：</p><ul><li value="1">对<b>“Start Time”</b>列标题进行透视，使其转换为一个名为<b>“Start”</b>的字符串值，并将该值添加到一个名为<b>“Action”</b>的新列中。</li><li value="2">对<b>“End Time”</b>列标题进行透视，使其转换为一个名为<b>“End”</b>的字符串值，并将该值添加到一个名为<b>“Action”</b>的新列中。</li><li value="3">对<b>“Start Time”</b>和<b>“End Time”</b>列进行透视，使其值在一个名为<b>“Time”</b>的新列中。</li></ul><p>下表显示了此自定义 SQL 查询的结果。</p><table style="width: 80%;mc-table-style: url('Resources/TableStyles/Basic-Border-Light.css');" class="TableStyle-Basic-Border" cellspacing="0"><colgroup><col class="TableStyle-Basic-Border-Column-Column1"/><col class="TableStyle-Basic-Border-Column-Column1"/><col class="TableStyle-Basic-Border-Column-Column1"/></colgroup><thead><tr class="TableStyle-Basic-Border-Head-Header1"><th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Runner</th><th class="TableStyle-Basic-Border-HeadE-Column1-Header1">Action</th><th class="TableStyle-Basic-Border-HeadD-Column1-Header1">Time</th></tr></thead><tbody><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Amanda</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1"><p>Start</p></td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:04 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Oscar</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Start</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:04 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">William</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Start</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:04 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Amanda</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">End</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:25 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">Oscar</td><td class="TableStyle-Basic-Border-BodyE-Column1-Body1">End</td><td class="TableStyle-Basic-Border-BodyD-Column1-Body1">9/3/2016 3:21 PM</td></tr><tr class="TableStyle-Basic-Border-Body-Body1"><td class="TableStyle-Basic-Border-BodyB-Column1-Body1">William</td><td class="TableStyle-Basic-Border-BodyB-Column1-Body1">End</td><td class="TableStyle-Basic-Border-BodyA-Column1-Body1">9/3/2016 3:16 PM</td></tr></tbody></table><p> </p><h3 is="heading-item" :level="3" id="使用自定义-sql-进行数据透视">使用自定义 SQL 进行数据透视</h3><ol><li value="1"><p>连接到数据。</p></li><li value="2"><p>双击左侧窗格中的<span class="uicontrol">“新建自定义 SQL”</span>选项。有关详细信息，请参见<a href="customsql.htm" class="MCXref xref">连接到自定义 SQL 查询</a>。</p></li><li value="3"><p>在<span class="uicontrol">“编辑自定义 SQL”</span>对话框中，复制并粘贴以下自定义 SQL 查询，并将内容替换为关于表的信息：</p><pre xml:space="preserve">Select [<i>Static Column</i>]</pre><pre xml:space="preserve">, '<i>New Value (from Column Header 1)</i>' as [<i>New Column Header</i>]</pre><pre xml:space="preserve">, [<i>Pivot Column Values 1</i>] as [<i>New Values</i>]</pre><pre xml:space="preserve">From [<i>Table</i>]</pre><pre xml:space="preserve">Union ALL</pre><pre xml:space="preserve">Select [<i>Static Column</i>]</pre><pre xml:space="preserve">, '<i>New Value (from Column Header 2</i>' as [<i>New Column Header</i>]</pre><pre xml:space="preserve">, [<i>Pivot Column Values 2</i>] as [<i>New Values</i>]</pre><pre xml:space="preserve">From [<i>Table</i>]</pre><pre xml:space="preserve">Union ALL</pre><pre xml:space="preserve">Select [<i>Static Column</i>]</pre><pre xml:space="preserve">, '<i>New Value (from Column Header 3</i>' as [<i>New Column Header</i>]</pre><pre xml:space="preserve">, [<i>Pivot Column Values 3</i>] as [<i>New Values</i>]</pre><pre xml:space="preserve">From [<i>Table</i>]</pre><p>如果以下条件为真：</p><ul><li value="1"><p><i>Static Column（静态列）</i>： <i>Table（表）</i>中列（维度和度量）的逗号分隔列表，不应该包括在行转置为列中。</p></li><li value="2"><p><i>New Value (from Column Header 1-3)（新值（来自列标题 1-3））</i>：您为原始列标题提供的新名称，用作行转置为列中的行值。</p></li><li value="3"><p><i>Pivot Column Values 1-3（行转置为列列值 1-3）</i>：需要对其值进行透视以转换成单一列的列。</p></li><li value="4"><p><i>New Column Header（新列标题）</i>：为新列提供的名称，此新列包含<i>“New Value (from Column Header 1-3)”（新值（来自列标题 1-3））</i>中的新行值。</p></li><li value="5"><p><i>New Values（新值）</i>：为新列提供的名称，此新列包含<i>“Pivot Column Values 1-3”（行转置为列列值 1-3）</i>中的原始值。</p></li><li value="6"><p><i>Table（表）</i>：连接到的表。</p></li></ul></li><li value="4"><p>单击<span class="uicontrol">“确定”</span>。</p></li></ol></div></div><div class="article__footer--back-to-top text--centered print-hidden quick-help-hidden"><a href="#" class="text--caps"><span class="icon--med icon--arrow-up"/> 回到顶部</a></div><feedback-form :disabled="false" class="print-hidden content-only-hidden" helpful-question="此内容是否有帮助?" helpful-yes="是" helpful-no="否" comment-question="我们重视您的反馈。在此处添加注释。" submit-option="发送" cancel-option="取消" loading-text="正在发送..."><span slot="submittedMessage">感谢您的反馈!</span></feedback-form></article><nav class="nav-side nav-side--right"><menu-heading-links-scrollable-help menu-title="本文内容" :disabled="false" :headings="pageHeadings"/></nav></div></div><feedback-button-floating tooltip="发送反馈" :disabled="false"/><footer class="container--full-width"><div class="container--centered"><div class="footer__links text--caps"><ul><li><a href="https://www.tableau.com/zh-cn/legal" class="">法律</a></li><li><a id="privacyLink" class="">隐私</a></li><li><a href="#" data-ignore-geolocation="true" class="optanon-toggle-display">Cookie Preferences</a></li></ul></div><div class="footer__copyright text--caps">© 2003-2022 Tableau Software LLC.保留所有权利</div></div></footer></div> </body>