SQL Server : split row into multiple rows based on a column value

Question

I have a question regarding splitting rows based on column value

My example data set is :

id   ExpenseType   Price
------------------------
 1   Car           100
 2   Hotel          50

I want to split rows those have some Expense Types such as Car into two rows . Others should remain as one row.

First row Price *70 
Second Row Price *30 

Returned dataset should be

id   ExpenseType  Price
-----------------------
 1   Car           70 
 1   Car           30 
 2   Hotel         50

Thanks for your answers in advance

Answer 1

I ran into a similar need, here is my solution.

Problem statement:
My organization is switching from an in-house build system to a third-party system. Numerical values in the original system surpassed the value size that the destination system could handle. The third-party system will not allow us to increase the field size, as a result we need to split the data up into values that do not surpass the field size limit.

Details: Destination system can only support values under 1 billion (can include a negative sign)

Example:

    DROP TABLE IF EXISTS #MyDemoData /* Generate some fake data for the demo */
            SELECT item_no = 1, item_description = 'zero asset',    amount = 0  INTO #MyDemoData
    UNION   SELECT item_no = 2, item_description = 'small asset',   amount = 5100000  
    UNION   SELECT item_no = 3, item_description = 'mid asset',     amount = 510000000  
    UNION   SELECT item_no = 4, item_description = 'large asset',   amount = 5100000000
    UNION   SELECT item_no = 5, item_description = 'large debt',    amount = -2999999999.99  
    

    SELECT * FROM #MyDemoData

    DECLARE @limit_size INT = 1000000000

    DROP TABLE IF EXISTS #groupings;
    WITH 
        max_groups AS 
            (   
                SELECT max_groups=100
            )
        ,groups AS 
            (
                SELECT 1 AS [group]
                UNION ALL
                SELECT [group]+1 
                FROM groups
                JOIN  max_groups ON 1=1 
                WHERE [group]+1<=max_groups
            )   
        ,group_rows AS 
            (
                SELECT 0 AS [row]
                UNION ALL
                SELECT [row]+1 
                FROM group_rows
                JOIN  max_groups ON 1=1 
                WHERE [row]+1<=max_groups
            )
        ,groupings AS
            (
                SELECT      [group],[row]
                FROM        group_rows
                CROSS JOIN  groups
                WHERE       [row] <= [group]
            )

        SELECT * INTO #groupings FROM groupings;

    WITH /* Split out items that are under the limit and over the limit */
        
        t1 AS /* Identify rows that are over the limit and by how many multiples over it is */
            (
                SELECT
                        item_no
                    ,   item_description    
                    ,   amount
                    ,   over_limit = FLOOR(ABS(amount/@limit_size))
                FROM    #MyDemoData
            )
    
    SELECT /* select the items that are under the limit and do not need manipulated */
                item_no
            ,   item_description    
            ,   amount = CAST(amount AS DECIMAL(16,2))
    FROM        t1 
    WHERE       ABS([amount]) < @limit_size
    UNION ALL /* select the items that are over the limit, join on the groupings cte and calculate the split amounts */
    SELECT
                item_no
            ,   item_description    
            ,   [Amount] = CAST(
                    CASE 
                            WHEN row != 0 THEN (@limit_size-1) * ([amount]/ABS([amount]))   
                            ELSE (ABS([amount]) - (t1.over_limit * @limit_size) + t1.over_limit) * ([amount]/ABS([amount]))
                    END AS DECIMAL(16,2))
    FROM        t1 
    JOIN        #groupings bg ON t1.over_limit = bg.[group] 
    WHERE       ABS([amount]) >= @limit_size
    ORDER BY    item_no

Answer 2

A less simple way is to use an OUTER APPLY

CREATE TABLE YourSampleData
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  ExpenseType VARCHAR(30) NOT NULL,
  Price INT NOT NULL DEFAULT 0
);

INSERT INTO YourSampleData 
(ExpenseType, Price) VALUES
 ('Car', 100)
,('Hotel', 50)
,('Gold', 1)
;

SELECT Id, ExpenseType
, COALESCE(a.Price, t.Price) AS Price
FROM YourSampleData t
OUTER APPLY
(
  SELECT Price * Perc AS Price
  FROM (VALUES 
    ('Car',0.3E0), ('Car',0.7E0)
   ,('Gold',1.618E0)
  ) AS v(ExpType, Perc)
  WHERE t.ExpenseType = v.ExpType
) a
GO

Id | ExpenseType | Price
-: | :---------- | ----:
 1 | Car         |    30
 1 | Car         |    70
 2 | Hotel       |    50
 3 | Gold        | 1.618

db<>fiddle here

Answer 3

If you want to split more expense types than car you could use:

WITH r AS (
    SELECT 'Car' AS ExpenseType, 0.7 AS Ratio
    UNION SELECT 'Car' AS ExpenseType, 0.3 AS Ratio
    -- add more ExpenseTypes/Ratios here
)
SELECT
    t.id,
    t.ExpenseType,
    t.Price * ISNULL(r.Ratio, 1.0) AS Price
FROM
    your_table t
    LEFT OUTER JOIN r ON t.ExpenseType = r.ExpenseType

Answer 4

A simple way uses union all:

select id, expensetype, price
from t
where expensetype <> 'Car'
union all
select id, expensetype, price * 0.7
from t
where expensetype = 'Car'
union all
select id, expensetype, price * 0.3
from t
where expensetype = 'Car';

This is not the most efficient method. For that, a cross apply with filtering logic is better:

select t.id, v.*
from t cross apply
     (values (NULL, price), ('Car', price * 0.3), ('Car', price * 0.7)
     ) v(expensetype, price)
where v.expensetype = t.expense_type or
      v.expensetype <> 'Car' and t.expense_type is null;