sub - t sql空白除去




如何使用GROUP BY連接SQL Server中的字符串? (11)

如何得到:

id       Name       Value
1          A          4
1          B          8
2          C          9

id          Column
1          A:4, B:8
2          C:9

SQL Server 2005和更高版本允許您創建自己的自定義集合函數 ,包括諸如拼接之類的東西 - 請參閱鏈接文章底部的示例。


無需CURSOR,WHILE循環或用戶定義的功能

只需要對FOR XML和PATH充滿創意。

[注意:此解決方案僅適用於SQL 2005及更高版本。 原始問題沒有指定使用的版本。]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

使用XML路徑不會像您所期望的那樣完美地連接在一起......它將用“&amp;”替換“&” 並且還會混淆<" and "> ...也許還有其他一些事情,不確定...但你可以試試這個

我遇到了一個解決方法...你需要替換:

FOR XML PATH('')
)

有:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

...或NVARCHAR(MAX)如果這就是你使用的。

為什麼地獄不SQL有一個連接集合函數? 這是一個PITA。



在Oracle中,您可以使用LISTAGG聚合函數。 一個例子是:

name   type
------------
name1  type1
name2  type2
name2  type3

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name

會導致:

name   type
------------
name1  type1
name2  type2; type3

如果group by主要包含一個項目,則可以通過以下方式顯著提高性能:

SELECT 
  [ID],

CASE WHEN MAX( [Name]) = MIN( [Name]) THEN 
MAX( [Name]) NameValues
ELSE

  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues

END

FROM #YourTable Results
GROUP BY ID

http://groupconcat.codeplex.com安裝SQLCLR聚合

然後你可以編寫這樣的代碼來得到你要求的結果:

CREATE TABLE foo
(
 id INT,
 name CHAR(1),
 Value CHAR(1)
);

INSERT  INTO dbo.foo
    (id, name, Value)
VALUES  (1, 'A', '4'),
        (1, 'B', '8'),
        (2, 'C', '9');

SELECT  id,
    dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM    dbo.foo
GROUP BY id;

沒有看到任何交叉應用的答案,也不需要進行XML提取。 這是Kevin Fairchild寫的一個稍微不同的版本。 在更複雜的查詢中使用它更快更容易:

   select T.ID
,MAX(X.cl) NameValues
 from #YourTable T
 CROSS APPLY 
 (select STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
    FROM #YourTable 
    WHERE (ID = T.ID) 
    FOR XML PATH(''))
  ,1,2,'')  [cl]) X
  GROUP BY T.ID

當我嘗試將Kevin Fairchild的建議轉換為使用包含空格和特殊XML字符( &<> )的字符串時,遇到了一些問題。

我的代碼的最終版本(不回答原始問題,但可能對某人有用)如下所示:

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT  [ID],
  STUFF((
    SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
    FROM #YourTable WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)') 
  ,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

而不是使用空格作為分隔符,並用逗號替換所有空格,它只是為每個值預留一個逗號和空格,然後使用STUFF刪除前兩個字符。

XML編碼通過使用TYPE指令自動處理。


讓我們變得非常簡單:

SELECT stuff(
    (
    select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb 
    FOR XML PATH('')
    )
, 1, 2, '')

替換這一行:

select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb

隨著你的查詢。


這種問題在這裡經常被問到,解決方案將很大程度上取決於底層需求:

https://.com/search?q=sql+pivot

https://.com/search?q=sql+concatenate

通常,在沒有動態sql,用戶定義的函數或游標的情況下,不存在僅使用SQL的方法。





sql-server-group-concat