Insert multiple rows from 1 row based on value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert multiple rows from 1 row based on value

Author

Topic

afaa
Starting Member

16 Posts

Posted - 2008-02-28 : 19:15:09

Hello all,
I have the following table:
CREATE TABLE [dbo].[TestTable1] (
[Code1] [varchar] (10),
[Code2] [varchar] (10))

The values of TestTable1 would be like this:
Code1 Code2
A X
A Y
B X
B Y

I want to transform this data into another table:
CREATE TABLE [dbo].[TestTable2] (
[SysId] [int] NULL
)

The desire output of TestTable2 would look like this:

If Code1 = A and Code2 = X
then insert 2 records and
TestTable2 would have:

SysID:
1
2

If Code1 = A and Code2 = Y
then insert 1 record into TestTable2

SysId:
2

If Code1 = B and Code2 = X
Then insert 2 records into TestTable2 like this:

SysID:
2
3

If Code1 = B and Code2 = Y
Then insert 1 record into TestTable2 like this:

SysID:
3

So in this example my TestTable2 would have 6 rows

SysID:
1
2
2
2
3
3

I would like to avoid using cursors because the table is very large. I am simplifying things a little bit here. In reality the results of TestTable1 comes from a fairly complex query that takes a long time to process. Note: there are 14 possible combinations of Code1 and Code2 if that makes any difference.

Any help is appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-28 : 20:59:42

using F_TABLE_NUMBER_RANGE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

DECLARE @TestTable1 TABLE
(
	[Code1] [varchar] (10),
	[Code2] [varchar] (10)
) 

INSERT INTO @TestTable1
SELECT	'A', 'X'	UNION ALL
SELECT	'A', 'Y'	UNION ALL
SELECT	'B', 'X'	UNION ALL
SELECT	'B', 'Y'

SELECT	n.NUMBER
FROM	@TestTable1 t
	CROSS JOIN dbo.F_TABLE_NUMBER_RANGE(1, 3) n
WHERE	(t.Code1	= 'A'	AND	t.Code2	= 'X'	AND	n.NUMBER <= 2)
OR	(t.Code1	= 'A'	AND	t.Code2	= 'Y'	AND	n.NUMBER  = 2)
OR	(t.Code1	= 'B'	AND	t.Code2	= 'X'	AND	n.NUMBER >= 2)
OR	(t.Code1	= 'B'	AND	t.Code2	= 'Y'	AND	n.NUMBER  = 3)

/*
NUMBER      
----------- 
          1 
          2 
          2 
          2 
          3 
          3 

(6 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

afaa
Starting Member

16 Posts

Posted - 2008-02-29 : 16:01:48

Thanks for the quick and detailed response.
I kind of derived to a similar but more low-tech solution. My question is regarding performance between a lookup table and a variable table. I was thinking about creating a lookup table like this:

CREATE TABLE [dbo].[lookup] (
[Code1Code2] [varchar] (10),
[Sysid] [varchar] (10))

Code1Code2|Sysid
AX|1
AX|2
AY|2
BX|2
BX|3
BY|3

Then do:
Select Code1 + Code2 as Code12,sysid from lookup
join TestTable1
on Code1Code2 = Code12

Please share your thoughts.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-29 : 23:35:14

with look up table

DECLARE @TestTable1 TABLE
(
	[Code1] [varchar] (10),
	[Code2] [varchar] (10)
) 

INSERT INTO @TestTable1
SELECT	'A', 'X'	UNION ALL
SELECT	'A', 'Y'	UNION ALL
SELECT	'B', 'X'	UNION ALL
SELECT	'B', 'Y'

DECLARE	@LookUp TABLE
(
	[Code1] [varchar] (10),
	[Code2] [varchar] (10),
	[SysID]	int
)

INSERT INTO @LookUp
SELECT	'A', 'X', 1	UNION ALL
SELECT	'A', 'X', 2	UNION ALL
SELECT	'A', 'Y', 2	UNION ALL
SELECT	'B', 'X', 2	UNION ALL
SELECT	'B', 'X', 3	UNION ALL
SELECT	'B', 'Y', 3

SELECT	l.SysID
FROM	@TestTable1 t
	INNER JOIN @LookUp l
	ON	t.Code1	= l.Code1
	AND	t.Code2	= l.Code2
/*
SysID       
----------- 
          1 
          2 
          2 
          2 
          3 
          3 

(6 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

afaa
Starting Member

16 Posts

Posted - 2008-03-03 : 19:15:35

khtan,
Thank you for taking the time to post. You've been very helpful.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-03 : 23:35:55

you are most welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources