CODEWITHSUNDEEP
sql-server
user3011414
user3011414

Reputation: 119

How to sum or group by text in sql

I'm using the SQL 2008 R2.

Here is my sample:

Class  Name1   Quantity1  Name2   Quantity2  Name3   Quantity3
AAA    Jane      6         ''        ''       ''       ''
AAA     ''       ''        Jim        12      ''       ''
AAA     ''       ''        ''        ''       Hons      2

How could I select data so that I have the result like that:

Class  Name1   Quantity1  Name2   Quantity2  Name3   Quantity3
AAA    Jane      6         Jim        12      Hons      2

Please advise.

Thanks.

Upvotes: 2

Views: 3469

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Assuming that all your columns are varchar (or a similar non numeric type), and that the empty cells in your sample data really are the empty string, then the following should work:

SELECT Class,
       MAX(CASE WHEN Name1     = '' THEN NULL ELSE Name1 END) AS Name1,
       MAX(CASE WHEN Quantity1 = '' THEN NULL ELSE Quantity1 END) AS Quantity1,
       MAX(CASE WHEN Name2     = '' THEN NULL ELSE Name2 END) AS Name2,
       MAX(CASE WHEN Quantity2 = '' THEN NULL ELSE Quantity2 END) AS Quantity2,
       MAX(CASE WHEN Name3     = '' THEN NULL ELSE Name3 END) AS Name3,
       MAX(CASE WHEN Quantity3 = '' THEN NULL ELSE Quantity3 END) AS Quantity3
FROM yourTable
GROUP BY Class

The trick here is that MAX() ignores NULL values in SQL Server. Here we want to ignore the empty cells and only take the real textual data. Another assumption is that you only have one piece of real data per column, per Class.

If you instead have NULL values where no real data be present, then you can get away with simply using MAX(col) for each column.

Upvotes: 3

gofr1
gofr1

Reputation: 15997

You can use MAX:

SELECT  [Class],
        MAX(Name1) as Name1,
        MAX(Quantity1 ) as Quantity1,
        MAX(Name2) as Name2,
        MAX(Quantity2) as Quantity2,
        MAX(Name3) as Name3,
        MAX(Quantity3) as Quantity3
FROM YourTableName
GROUP BY [Class]

Upvotes: 2

Related Questions