I have something like this:
id | name | totalAmount |
---|---|---|
1 | name1 | 10 |
2 | name1 | 20 |
3 | name1 | 25 |
4 | name2 | 5 |
5 | name2 | 12 |
And need to looks like this:
id's | name | totalAmount |
---|---|---|
1,2 | name1 | 30 |
2,3 | name1 | 45 |
1,3 | name1 | 35 |
1,2,3 | name1 | 55 |
4,5 | name2 | 17 |
I'm using the STRING_AGG
but don't know how to separated in the first 3 id's.
CodePudding user response:
Here is a recursive version which can handle more than 3 ids for a name and returns all possible combinations. As Dai points out though, take care as the number of combinations quickly mushrooms. But if your real data is like your example (normally 2-3 ids per name) than it should be fine.
Worth noting that I did this for fun. Probably you would be best just storing the raw data and doing this kind of shenanigans in the application layer.
CREATE TABLE #data
(
id INT,
[name] VARCHAR(10),
totalAmount INT
);
INSERT INTO #data
VALUES
(1, 'name1', 10),
(2, 'name1', 20),
(3, 'name1', 25),
(4, 'name2', 5),
(5, 'name2', 12);
WITH cte (name, ids, maxid, tot) AS
(
SELECT a.name,
CONVERT(VARCHAR(8000), CONVERT(VARCHAR(10), a.id) ',' CONVERT(VARCHAR(10), b.id) ) AS ids,
b.id AS maxid,
a.totalAmount b.totalAmount AS tot
FROM #data a
INNER JOIN #data b ON b.name = a.name AND a.id < b.id
UNION ALL
SELECT cte.name,
CONVERT(VARCHAR(8000), cte.ids ',' CONVERT(VARCHAR(10), a.id)),
a.id AS maxid,
cte.tot a.totalAmount
FROM cte
INNER JOIN #data a ON cte.name = a.name
WHERE a.id > cte.maxid
)
SELECT ids, name, tot
FROM cte
CodePudding user response:
-- *** Test Data ***
CREATE TABLE #t
(
id int NOT NULL PRIMARY KEY
,[name] nvarchar(30) NOT NULL
,totalAmount money NOT NULL
);
INSERT INTO #t
VALUES (1, 'name1', 10)
,(2, 'name1', 20)
,(3, 'name1', 25)
,(4, 'name2', 5)
,(5, 'name2', 12);
-- *** End Test Data ***
SELECT CAST(T1.id AS varchar(10))
',' CAST(T2.id AS varchar(10)) AS ids
,T1.[name] AS [name]
,T1.totalAmount T2.totalAmount AS totalAmount
FROM #t T1
JOIN #t T2
ON T1.[name] = T2.[name]
WHERE T1.id < T2.id
UNION ALL
SELECT CAST(T1.id AS varchar(10))
',' CAST(T2.id AS varchar(10))
',' CAST(T3.id AS varchar(10)) AS ids
,T1.[name] AS [name]
,T1.totalAmount T2.totalAmount T3.totalAmount AS totalAmount
FROM #t T1
JOIN #t T2
ON T1.[name] = T2.[name]
JOIN #t T3
ON T1.[name] = T3.[name]
WHERE T1.id < T2.id
AND T2.id < T3.id;