<small id='mKJbQ'></small><noframes id='mKJbQ'>

<i id='mKJbQ'><tr id='mKJbQ'><dt id='mKJbQ'><q id='mKJbQ'><span id='mKJbQ'><b id='mKJbQ'><form id='mKJbQ'><ins id='mKJbQ'></ins><ul id='mKJbQ'></ul><sub id='mKJbQ'></sub></form><legend id='mKJbQ'></legend><bdo id='mKJbQ'><pre id='mKJbQ'><center id='mKJbQ'></center></pre></bdo></b><th id='mKJbQ'></th></span></q></dt></tr></i><div id='mKJbQ'><tfoot id='mKJbQ'></tfoot><dl id='mKJbQ'><fieldset id='mKJbQ'></fieldset></dl></div>

    <legend id='mKJbQ'><style id='mKJbQ'><dir id='mKJbQ'><q id='mKJbQ'></q></dir></style></legend>
    1. <tfoot id='mKJbQ'></tfoot>

        <bdo id='mKJbQ'></bdo><ul id='mKJbQ'></ul>
      1. 转置没有聚合的行和列

        Transpose rows and columns with no aggregate(转置没有聚合的行和列)

            <tfoot id='BQkl9'></tfoot>
                <bdo id='BQkl9'></bdo><ul id='BQkl9'></ul>

                <small id='BQkl9'></small><noframes id='BQkl9'>

                  <tbody id='BQkl9'></tbody>
                1. <i id='BQkl9'><tr id='BQkl9'><dt id='BQkl9'><q id='BQkl9'><span id='BQkl9'><b id='BQkl9'><form id='BQkl9'><ins id='BQkl9'></ins><ul id='BQkl9'></ul><sub id='BQkl9'></sub></form><legend id='BQkl9'></legend><bdo id='BQkl9'><pre id='BQkl9'><center id='BQkl9'></center></pre></bdo></b><th id='BQkl9'></th></span></q></dt></tr></i><div id='BQkl9'><tfoot id='BQkl9'></tfoot><dl id='BQkl9'><fieldset id='BQkl9'></fieldset></dl></div>
                  <legend id='BQkl9'><style id='BQkl9'><dir id='BQkl9'><q id='BQkl9'></q></dir></style></legend>
                  本文介绍了转置没有聚合的行和列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有以下数据集

                  Account Contact
                  
                  1   324324324
                  1   674323234
                  2   833343432
                  2   433243443
                  3   787655455
                  4   754327545
                  4   455435435
                  5   543544355
                  5   432455553
                  5   432433242
                  5   432432432
                  

                  我希望输出如下:

                  Account Contact1    Contact2    Contact3    Contact4
                  
                  1   324324324   674323234       
                  2   833343432   433243443       
                  3   787655455           
                  4   754327545   455435435       
                  5   543544355   432455553   432433242   432432432
                  

                  问题还在于我有一个不固定数量的 Accounts &不固定数量的联系人

                  The problem is also that I have an unfixed amount of Accounts & unfixed amount of Contacts

                  推荐答案

                  如果您要应用 PIVOT 函数,您将需要使用聚合函数来获得结果,但您也将想使用像 row_number() 这样的窗口函数来为帐户中的每个联系人生成唯一的序列.

                  If you are going to apply the PIVOT function, you will need to use an aggregate function to get the result but you will also want to use a windowing function like row_number() to generate a unique sequence for each contact in the account.

                  首先,您将查询类似于以下内容的数据:

                  First, you will query your data similar to:

                  select account, contact,
                    'contact'
                      + cast(row_number() over(partition by account
                                                order by contact) as varchar(10)) seq
                  from yourtable
                  

                  参见SQL Fiddle with Demo.这将创建一个具有唯一序列的新列:

                  See SQL Fiddle with Demo. This will create a new column with the unique sequence:

                  | ACCOUNT |   CONTACT |      SEQ |
                  |---------|-----------|----------|
                  |       1 | 324324324 | contact1 |
                  |       1 | 674323234 | contact2 |
                  

                  如果您的列数有限,那么您可以对查询进行硬编码:

                  If you have a limited number of columns, then you could hard-code your query:

                  select account,
                    contact1, contact2, contact3, contact4
                  from 
                  (
                    select account, contact,
                      'contact'
                        + cast(row_number() over(partition by account
                                                  order by contact) as varchar(10)) seq
                    from yourtable
                  ) d
                  pivot
                  (
                    max(contact)
                    for seq in (contact1, contact2, contact3, contact4)
                  ) piv;
                  

                  参见SQL Fiddle with Demo

                  如果您有未知数量的列,那么您将不得不使用动态 SQL:

                  If you have an unknown number of columns, then you will have to use dynamic SQL:

                  DECLARE @cols AS NVARCHAR(MAX),
                      @query  AS NVARCHAR(MAX)
                  
                  select @cols = STUFF((SELECT ',' + QUOTENAME(seq) 
                                      from
                                      (
                                        select 'contact'
                                                + cast(row_number() over(partition by account
                                                                          order by contact) as varchar(10)) seq
                                        from yourtable
                                      ) d
                                      group by seq
                                      order by seq
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)') 
                          ,1,1,'')
                  
                  set @query = 'SELECT account, ' + @cols + ' 
                              from 
                              (
                                  select account, contact,
                                    ''contact''
                                      + cast(row_number() over(partition by account
                                                                order by contact) as varchar(10)) seq
                                  from yourtable
                              ) x
                              pivot 
                              (
                                  max(contact)
                                  for seq in (' + @cols + ')
                              ) p '
                  
                  execute sp_executesql @query;
                  

                  参见SQL Fiddle with Demo.两者都会给你一个结果:

                  See SQL Fiddle with Demo. Both will give you a result of:

                  | ACCOUNT |  CONTACT1 |  CONTACT2 |  CONTACT3 |  CONTACT4 |
                  |---------|-----------|-----------|-----------|-----------|
                  |       1 | 324324324 | 674323234 |    (null) |    (null) |
                  |       2 | 433243443 | 833343432 |    (null) |    (null) |
                  |       3 | 787655455 |    (null) |    (null) |    (null) |
                  |       4 | 455435435 | 754327545 |    (null) |    (null) |
                  |       5 | 432432432 | 432433242 | 432455553 | 543544355 |
                  

                  这篇关于转置没有聚合的行和列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Building a comma separated list?(建立一个逗号分隔的列表?)
                  Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column(尽管每列都使用了 varchar(MAX),但在导入 CSV 文件时 SQL Server 中出现错误)
                  How can I import an Excel file into SQL Server?(如何将 Excel 文件导入 SQL Server?)
                  Export table to file with column headers (column names) using the bcp utility and SQL Server 2008(使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件)
                  Concat field value to string in SQL Server(将字段值连接到 SQL Server 中的字符串)
                  SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)
                      <tbody id='8gwqu'></tbody>

                  • <small id='8gwqu'></small><noframes id='8gwqu'>

                    <tfoot id='8gwqu'></tfoot>

                            <bdo id='8gwqu'></bdo><ul id='8gwqu'></ul>
                          • <i id='8gwqu'><tr id='8gwqu'><dt id='8gwqu'><q id='8gwqu'><span id='8gwqu'><b id='8gwqu'><form id='8gwqu'><ins id='8gwqu'></ins><ul id='8gwqu'></ul><sub id='8gwqu'></sub></form><legend id='8gwqu'></legend><bdo id='8gwqu'><pre id='8gwqu'><center id='8gwqu'></center></pre></bdo></b><th id='8gwqu'></th></span></q></dt></tr></i><div id='8gwqu'><tfoot id='8gwqu'></tfoot><dl id='8gwqu'><fieldset id='8gwqu'></fieldset></dl></div>
                            <legend id='8gwqu'><style id='8gwqu'><dir id='8gwqu'><q id='8gwqu'></q></dir></style></legend>