How to take a single record and split it into 2 equal valued records.

[cam10]

In a stored procedure (with many nested select statements) I am trying to split one particular record into 2 equal records with equal values. So, for example, if I come across a record with a description of "dog" and it has an associated numeric value of 30, I want to split it into 2 records both with a description of "dog" with numeric values of 15 for each. I want to do this for each period in a fiscal year.

What I am struggling with is this statement:
select Aname
,CASE WHEN Aname = 'dog' THEN Round(p1/2, 0) ELSE p1 END

This only creates a single record (1 column) because of the ELSE statement. How do I create a 2nd row (record). Keep in mind this is just for display, it won't change the database.


Sample of code:

USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test_proc]
	@var1 int,
	@var2 varchar(10)
AS
BEGIN
	DECLARE temp int

select Aname
	,CASE WHEN Aname = 'dog' THEN Round(p1/2, 0) ELSE p1 END
from
	(select list…..
from 
	(select list…..
	
from dbDog a, dbDogList b                                                              
	where a.key = b.key
	and …..) temptable
	group by ….) temptable2) temptable3
	group by … )temptable4 where ….
	and….)temptable5
	order by ……

	DROP TABLE #PeriodEndDates

END

 

[RyanEK]

Hi,

There are probably several ways to do this. How about using CTE?

declare @temp table (aname varchar(5), value int)
insert into @temp values ('dog', 30)
insert into @temp values ('cat', 50)
insert into @temp values ('horse', 100)

;with Calculated (aname, value) as
(
  select aname,
  case when aname in ('dog','cat') then value/2 else value end as value
  from @temp
)

select * 
from calculated
union all
select c.* 
from calculated c
inner join @temp t on c.aname = t.aname and c.value != t.value
order by aname

Ryan

 

[cam10]

Thanks, this is more or less what I ended up doing.
1. I created a temp table
2. Inserted the values of the multiple select query into the temp table.
3. Updated the value of the record in question (in the temp table) and divided by 2
4. Inserted a second record based on 3 above.

Thanks for your response!