1. <small id='ETy5u'></small><noframes id='ETy5u'>

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

        在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?

        Simulating group_concat MySQL function in Microsoft SQL Server 2005?(在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?)
            <tbody id='dmknm'></tbody>

              <tfoot id='dmknm'></tfoot>

              <legend id='dmknm'><style id='dmknm'><dir id='dmknm'><q id='dmknm'></q></dir></style></legend>

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

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

                  本文介绍了在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我正在尝试将基于 MySQL 的应用程序迁移到 Microsoft SQL Server 2005(不是出于选择,但就是这样).

                  I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).

                  在最初的应用程序中,我们几乎使用了完全符合 ANSI-SQL 的语句,但有一个重要的例外——我们相当频繁地使用 MySQL 的 group_concat 函数.

                  In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently.

                  group_concat 是这样做的:给定一个表,比如员工姓名和项目......

                  group_concat, by the way, does this: given a table of, say, employee names and projects...

                  SELECT empName, projID FROM project_members;
                  

                  返回:

                  ANDY   |  A100
                  ANDY   |  B391
                  ANDY   |  X010
                  TOM    |  A100
                  TOM    |  A510
                  

                  ...这是您使用 group_concat 获得的结果:

                  ... and here's what you get with group_concat:

                  SELECT 
                      empName, group_concat(projID SEPARATOR ' / ') 
                  FROM 
                      project_members 
                  GROUP BY 
                      empName;
                  

                  返回:

                  ANDY   |  A100 / B391 / X010
                  TOM    |  A100 / A510
                  

                  所以我想知道的是:是否可以在 SQL Server 中编写一个用户定义的函数来模拟 group_concat 的功能?

                  So what I'd like to know is: Is it possible to write, say, a user-defined function in SQL Server which emulates the functionality of group_concat?

                  我几乎没有使用 UDF、存储过程或类似的东西的经验,只是直接的 SQL,所以请不要过多解释:)

                  I have almost no experience using UDFs, stored procedures, or anything like that, just straight-up SQL, so please err on the side of too much explanation :)

                  推荐答案

                  没有真正简单的方法可以做到这一点.不过,这里有很多想法.

                  No REAL easy way to do this. Lots of ideas out there, though.

                  我找到的最好的一个:

                  SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
                  FROM information_schema.columns AS extern
                  CROSS APPLY
                  (
                      SELECT column_name + ','
                      FROM information_schema.columns AS intern
                      WHERE extern.table_name = intern.table_name
                      FOR XML PATH('')
                  ) pre_trimmed (column_names)
                  GROUP BY table_name, column_names;
                  

                  或者如果数据可能包含诸如 <

                  Or a version that works correctly if the data might contain characters such as <

                  WITH extern
                       AS (SELECT DISTINCT table_name
                           FROM   INFORMATION_SCHEMA.COLUMNS)
                  SELECT table_name,
                         LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
                  FROM   extern
                         CROSS APPLY (SELECT column_name + ','
                                      FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                                      WHERE  extern.table_name = intern.table_name
                                      FOR XML PATH(''), TYPE) x (column_names)
                         CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 
                  

                  这篇关于在 Microsoft SQL Server 2005 中模拟 group_concat MySQL 函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

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

                  相关文档推荐

                  Select n random rows from SQL Server table(从 SQL Server 表中随机选择 n 行)
                  SQL query to select dates between two dates(用于选择两个日期之间的日期的 SQL 查询)
                  How can I delete using INNER JOIN with SQL Server?(如何在 SQL Server 中使用 INNER JOIN 进行删除?)
                  Table Naming Dilemma: Singular vs. Plural Names(表命名困境:单数与复数名称)
                  INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server(INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server)
                  How ROWNUM works in pagination query?(ROWNUM 如何在分页查询中工作?)
                    <tbody id='uHs9s'></tbody>

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

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

                    <tfoot id='uHs9s'></tfoot>
                      • <bdo id='uHs9s'></bdo><ul id='uHs9s'></ul>

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