Creating Function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Creating Function

Author

Topic

daudi
Starting Member

2 Posts

Posted - 2007-01-17 : 08:05:42

Am abit new to SQL Server Programming and I have an issue which I need Assistance.

I have a table with the following Colunms
Inv_Number
ItemCode1
ItemCode2
ItemCode3
ItemCode4
ItemCode5
this table as data which i want to put to another table with the following Columns
Invoice_Number
ItemCode
Amount

What i mean here is that Invoice Items were static and were about five and the new programs am designing will have more item codes.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-17 : 08:11:17

Like this?

Insert into NewTable (Invoice_number, ItemCode, Amount)
Select Inv_Number,'1', ItemCode1 from OldTable union all
Select Inv_Number,'2', ItemCode2 from OldTable union all
Select Inv_Number,'3', ItemCode3 from OldTable union all
Select Inv_Number,'4', ItemCode4 from OldTable union all
Select Inv_Number,'5', ItemCode5 from OldTable

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-17 : 11:18:52

The best thing you can learn about SQL programming is how to normalize your data; data should never be stored in the manner you are describing.

see: http://www.datamodel.org/NormalizationRules.html

- Jeff

daudi
Starting Member

2 Posts

Posted - 2007-01-18 : 04:23:56

thanks alot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 04:49:18

What is Amount column?

INSERT		Table2
		(
			Inv_Number,
			ItemCode,
			Amount
		)
SELECT		Inv_Number,
		ItemCode,
		COUNT(*)
FROM		(
			SELECT	Inv_Number,
				ItemCode1 AS ItemCode
			FROM	Table1
			UNION ALL
			SELECT	Inv_Number,
				ItemCode2
			FROM	Table1
			UNION ALL
			SELECT	Inv_Number,
				ItemCode3
			FROM	Table1
			UNION ALL
			SELECT	Inv_Number,
				ItemCode4
			FROM	Table1
			UNION ALL
			SELECT	Inv_Number,
				ItemCode5
			FROM	Table1
		) AS x

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources