求把多行合并到一行的方法

[ajdkjalj]

DECLARE @t TABLE (csup_id nvarchar(50),md money)
INSERT INTO @t VALUES ('20170026',4554.00)
INSERT INTO @t VALUES ('20170024',-212.00)
INSERT INTO @t VALUES ('20170024',989.00)
INSERT INTO @t VALUES ('20170026',98987.00)

我想得到的结果：20170026，4554.00+98987.00；20170024，-212.00+989.00

[中国风]

这样？ e.g.

DECLARE @t TABLE (csup_id nvarchar(50),md money)
INSERT INTO @t VALUES ('20170026',4554.00)
INSERT INTO @t VALUES ('20170024',-212.00)
INSERT INTO @t VALUES ('20170024',989.00)
INSERT INTO @t VALUES ('20170026',98987.00)

--我想得到的结果：20170026，4554.00+98987.00；20170024，-212.00+989.00
;WITH t
AS
(
    SELECT *
         , ROW_NUMBER() OVER (ORDER BY RAND()) AS MinRN
    FROM @t)
    , t2
AS
(
    SELECT csup_id
         , MinRN
         , ROW_NUMBER() OVER (PARTITION BY csup_id ORDER BY MinRN) AS RN
    FROM t)
SELECT STUFF((SELECT ';'+csup_id+','+STUFF((SELECT '+'+LTRIM(STR(md, 18, 2))
                          FROM @t
                          WHERE csup_id=t.csup_id
                         FOR XML PATH(''))
                       , 1
                       , 1
                       , '') 

FROM t2 AS t
WHERE RN=1
ORDER BY MinRN
FOR XML PATH('')),1,1,'');
/*
(无列名)
20170026,4554.00+98987.00;20170024,-212.00+989.00
*/

[中国风]

DECLARE @t TABLE (csup_id nvarchar(50),md money)
INSERT INTO @t VALUES ('20170026',4554.00)
INSERT INTO @t VALUES ('20170024',-212.00)
INSERT INTO @t VALUES ('20170024',989.00)
INSERT INTO @t VALUES ('20170026',98987.00)

--我想得到的结果：20170026，4554.00+98987.00；20170024，-212.00+989.00


SELECT csup_id,STUFF((SELECT '+'+FORMAT(md,'0.00') FROM @t WHERE csup_id=t.csup_id FOR XML PATH('')),1,1,'') AS md FROM @t AS t GROUP BY csup_id
/*
csup_id	md
20170024	-212.00+989.00
20170026	4554.00+98987.00
*/

[中国风]

旧版如：SQL08之前版本可用

SELECT csup_id,STUFF((SELECT '+'+LTRIM(STR(md,18,2)) FROM @t WHERE csup_id=t.csup_id FOR XML PATH('')),1,1,'') AS md FROM @t AS t GROUP BY csup_id
/*
csup_id	md
20170024	-212.00+989.00
20170026	4554.00+98987.00
*/

[ajdkjalj]

引用 2 楼 roy_88 的回复:

旧版如：SQL08之前版本可用

SELECT csup_id,STUFF((SELECT '+'+LTRIM(STR(md,18,2)) FROM @t WHERE csup_id=t.csup_id FOR XML PATH('')),1,1,'') AS md FROM @t AS t GROUP BY csup_id
/*
csup_id	md
20170024	-212.00+989.00
20170026	4554.00+98987.00
*/

不是分成了两行，是一个字段，比如mx，mx显示：20170024，-212.00+989.00；20170026，4554.00+98987.00 说下业务逻辑吧，csup_id是供应商id，md是供应商的账款，想要得到不同供应商账款的明细，是显示在一个字段里