0

I think it's a really basic question, but I couldn't find the answer how to do it in SQL Server. I have a table:

ID Pref_01 Pref_02 ... Pref_40
01 5 2 ... 7
02 6 7 ... 5

I want my output to contain two columns: ID, Sum_pref with the sum of columns which names stars with "pref". I know how to do it in R or SQL in SAS but now I have to do it in SQL Server and I'm stuck. I will be greatful for any help.

Aleksandra
  • 151
  • 8
  • 1
    One method would be to unpivot your data, and then `SUM` it. The other would be to simply use basic addition: `Pref01 + Pref02 + ... + Pref_40`. I suspect that what you actually have is a denormalised design here. – Thom A Dec 12 '22 at 11:10
  • The basic addition is not a solution. In original data i have many more columns and different prefixes so it would be to many work. I'm looking for something like sum(of Prefix:) in SAS – Aleksandra Dec 12 '22 at 11:16
  • There is no such feature. If you want to use a `SUM` you'll need to unpivot your data first and then `SUM` the column. – Thom A Dec 12 '22 at 11:19
  • That's bad :(. Could you provide an example how to do this using unpivot? – Aleksandra Dec 12 '22 at 11:21
  • What's bad? And there's are lots, for example: [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) – Thom A Dec 12 '22 at 11:31

1 Answers1

0

Please try the following solution.

It is using SQL Server's XML and XQuery functionality to achieve what you need.

Notable points:

  1. 1st CROSS APPLY is tokenizing columns for each row as XML.
  2. 2nd CROSS APPLY is summarizing values where XML element names (and original column names) are matching a particular naming convention.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, Pref_01 INT, Pref_02 INT, Pref_40 INT);
INSERT @tbl (Pref_01, Pref_02, Pref_40) VALUES
(5, 2, 7),
(6, 7, 5);
-- DDL and sample data population, end

SELECT t.*
   , [sum-columns-across-the-row]
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x)
CROSS APPLY (SELECT x.value('sum(/root/*[contains(local-name(), "Pref_")]/text()) cast as xs:integer?', 'INT')) AS t2([sum-columns-across-the-row]);

Output

id Pref_01 Pref_02 Pref_40 sum-columns-across-the-row
1 5 2 7 14
2 6 7 5 18
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21