将所有行Concat分成一个单独的字符串 [英] Concat all rows into one single string

问题描述

亲爱的所有人，

我有一个输入表，如下所示。下表包含两个月内的4周数据。

I have one input table like the below. The below table contains 4 weeks of data in two Months.

我需要每周将两个月的数据连接成另一个星期的组名称。这些都在单行中。

i need to  get two month of data in each week concatenate into another week  group into name. these all in single row.

注意：

1.所有行分组到名称列中，然后连接成单行。

1. All rows grouped into Name Column and then concatenate into single row.

2.需要在动态查询的帮助下获得结果。

2. need to get the result with the help of dynamic queries.

推荐答案

这仅适用于 SQL Server 2016 以及。

如果我没有弄错，下面的代码应该会给你想要的结果。变量@j包含一个JSON文档，对周数或日期不应有任何限制。

Provided I did not get anything wrong, the code below should give you the desired result. The variable @j contains a JSON document which should not have any limitation on the number of weeks or dates.

使用OPENJSON（）的SELECT必须定义结果集中的列，所以它有10周/日期的限制。（请注意，表中只有8个，因此结果集在后两个中为NULL。）

The SELECT using OPENJSON() must define the columns in the result set, so it has a limitation of 10 weeks/dates. (Note that there are only 8 in the table, so the resultset has NULL for the last two.)

正如Erland Sommarskog在previos中指出的那样发布，这真的是那样的应该在表示层完成。但是，SQL Server 2016可以通过返回JSON来帮助我们。

As Erland Sommarskog pointed out in a previos post, this is really something that should be done in the presentation layer. However, SQL Server 2016 can help a little bit by returning JSON.

CREATE TABLE #tbl (
         Name    varchar(30) NOT NULL 
        ,Weeks   varchar(22) NOT NULL 
        ,Date    date        NOT NULL 
        ,DueDate date        NOT NULL )

INSERT #tbl(
         Name    
        ,Weeks   
        ,Date    
        ,DueDate  )
          SELECT 'TamilSelvan', '1st Week', '20190101', '20190101'
UNION ALL SELECT 'TamilSelvan', '2nd Week', '20190108', '20190108'
UNION ALL SELECT 'TamilSelvan', '3rd Week', '20190115', '20190122'
UNION ALL SELECT 'TamilSelvan', '4th Week', '20190122', '20190122'
UNION ALL SELECT 'TamilSelvan', '1st Week', '20190201', '20190201'
UNION ALL SELECT 'TamilSelvan', '2nd Week', '20190208', '20190208'
UNION ALL SELECT 'TamilSelvan', '3rd Week', '20190215', '20190222'
UNION ALL SELECT 'TamilSelvan', '4th Week', '20190222', '20190222'

-- Create a JSON doc in the desiredformat. This is has no limits on the number of weeks or names.
DECLARE @j nvarchar(MAX)
SELECT @j = (
   SELECT DISTINCT   
            t.Name AS "Name"
           ,JSON_QUERY((SELECT   Weeks
                        FROM     #tbl tw
                        WHERE    tw.Name = t.Name
                        FOR JSON AUTO)) AS "Weeks"
           ,JSON_QUERY((SELECT   Date 
                        FROM     #tbl tw
                        WHERE    tw.Name = t.Name
                        FOR JSON AUTO)) AS "Dates"
           ,JSON_QUERY( ( SELECT    DueDate
                FROM     #tbl tw
                WHERE    tw.Name = t.Name
                FOR JSON AUTO)) AS "DueDates"
   FROM     #tbl t
   FOR JSON PATH)

-- This is just a presentation of the JSON document in SQL Server Management Studio 
-- and has a limit of 10 rows (Weeks) per Name.
SELECT   *
FROM     OPENJSON(@j)
WITH (Name varchar(30) '

.Name'

，Weeks varchar（22）'

.Name' ,Weeks varchar(22) '

.Weeks [0] .Weeks'
，Dates varchar（30）'

.Weeks[0].Weeks' ,Dates varchar(30) '

这篇关于将所有行Concat分成一个单独的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！