By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE mytable (
element VARCHAR(20) ,
score INTEGER );
INSERT INTO mytable VALUES
('ABC',50),
('BAC',10),
('CBA',30),
('XYZ',115),
('ZXY',25);
5 rows affected
select * from mytable;
| element | score |
|---|---|
| ABC | 50 |
| BAC | 10 |
| CBA | 30 |
| XYZ | 115 |
| ZXY | 25 |
-- split the strings (currently fixed to max 3 positions...)
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
| element | score | x | Y |
|---|---|---|---|
| ABC | 50 | 1 | A |
| ABC | 50 | 2 | B |
| ABC | 50 | 3 | C |
| BAC | 10 | 1 | B |
| BAC | 10 | 2 | A |
| BAC | 10 | 3 | C |
| CBA | 30 | 1 | C |
| CBA | 30 | 2 | B |
| CBA | 30 | 3 | A |
| XYZ | 115 | 1 | X |
| XYZ | 115 | 2 | Y |
| XYZ | 115 | 3 | Z |
| ZXY | 25 | 1 | Z |
| ZXY | 25 | 2 | X |
| ZXY | 25 | 3 | Y |
-- create element_new
SELECT
element,
score,
STRING_AGG(y,'') WITHIN GROUP (order by y ) as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score;
| element | score | element_new |
|---|---|---|
| BAC | 10 | ABC |
| ZXY | 25 | XYZ |
| CBA | 30 | ABC |
| ABC | 50 | ABC |
| XYZ | 115 | XYZ |
-- do your thing
SELECT
element_new,
sum(score) as score
FROM (
SELECT
element,
score,
STRING_AGG(y,'') WITHIN GROUP (order by y ) as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score
) z
GROUP BY element_new;
| element_new | score |
|---|---|
| ABC | 90 |
| XYZ | 140 |