CODEWITHSUNDEEP
sqlsql-servert-sql
Michael Kovattil
Michael Kovattil

Reputation: 23

split data row wise based on row values

I have a table that stores information in the below format.

enter image description here

id, value , property are the columns. I have a requirement now to sum up data based on property.

i.e for property column F2 and Value,

I need values summed up and displayed as below:

Type | Sum
Cars   |  1892+702+515 
Bikes  | 1393 +0 + 474.6

Note: I know this is not the way to store data in a table, but table alterations are currently not possible.

Appreciate if you could give your inputs on this.

Upvotes: 0

Views: 404

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This looks like a really bad design. It looks like you are using the positions in the table to assign "groupings". Fortunately, you have an id column, so this is possible to do in SQL.

Here is the idea: First assign the appropriate F2 property to each row. Then do an aggregation. This following uses outer apply for the first part and group by for the second:

select t2.value,
       sum(case when isnumeric(t.value) = 1 then cast(t.value as decimal(10, 2))
           end) as thesum
from t outer apply
     (select top 1 t2.*
      from t t2
      where t2.id <= t.id and t2.property = 'F2'
      order by t2.id desc
     ) t2
group by t2.value;

This doesn't filter out the first group (all 0's). You can do that with an additional WHERE clause if you like.

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7928

Here's another solution which uses LEAD in case if you are running SQL Server 2012+ (note my comments).

-- Sample data
DECLARE @yourtable 
TABLE 
(
  id       int identity primary key, -- emulate an index on ID 
  value    varchar(100), 
  property varchar(5)
);

INSERT @yourtable (value, property) VALUES 
('0',     'F2'),
('0',     'V1'),
('0',     'V2'),
('0',     'V3'),
('Cars',  'F2'),
('1892',  'V1'),
('702',   'V2'),
('515',   'V3'),
('Bikes', 'F2'),
('1393',  'V1'),
('0',     'V2'),
('474.6', 'V2');

-- Solution
WITH startPoints AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY id)
  FROM @yourtable 
),
groups AS
(
  SELECT value, rn, ttl = 
    ISNULL(LEAD(id,1) OVER (ORDER BY id), (SELECT COUNT(*) FROM @yourtable)+1) - (rn+1)
  FROM startPoints
  WHERE property = 'F2' AND value LIKE ('%[^0-9.]%')
)
SELECT
  value, 
  SUM = 
  (
    SELECT SUM(CAST(value AS decimal(10,2)))
    FROM startPoints s
    WHERE s.rn BETWEEN g.rn+1 AND g.rn+ttl
  )
FROM groups g;

Upvotes: 1

Related Questions