Convert single record to table

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 2005 Forums

Transact-SQL (2005)

Convert single record to table

Author

Topic

SteveH
Starting Member

27 Posts

Posted - 2007-09-20 : 12:35:52

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks

Steve

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 12:50:59

Hopefully yuo don't have too many Columns Steve
INSERT INTO yourNewTable
INSERT INTO yourNewTable
SELECT Accnt1, Assessed1, Paid1
FROM yourOldTable
UNION ALL
SELECT Accnt2, Assessed2, Paid2
FROM yourOldTable
UNION ALL
SELECT Accnt3, Assessed3, Paid3
FROM yourOldTable
UNION ALL
SELECT Accnt4, Assessed4, Paid4
FROM yourOldTable
UNION ALL
SELECT Accnt5, Assessed5, Paid5
FROM yourOldTable
UNION ALL
SELECT Accnt6, Assessed6, Paid6
FROM yourOldTable
UNION ALL
SELECT Accnt7, Assessed7, Paid7
FROM yourOldTable
UNION ALL
SELECT Accnt8, Assessed8, Paid8
FROM yourOldTable
UNION ALL
SELECT Accnt9, Assessed9, Paid9
FROM yourOldTable
UNION ALL
SELECT Accnt10, Assessed10, Paid10
FROM yourOldTable

Jim

SteveH
Starting Member

27 Posts

Posted - 2007-09-21 : 11:23:12

Thanks, Jim. This is a great jumpstart!

Steve

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:40:10

Here's a bit of code you can hack somewhat to get a list of the columns in an appropriate order, and then covert into the sort of code that JimF posted:


SELECT COLUMN_NAME
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	    TABLE_NAME = 'yourOldTable'
	AND 
	(
		   COLUMN_NAME LIKE 'Accnt[0-9]%'
		OR COLUMN_NAME LIKE 'Assessed[0-9]%'
		OR COLUMN_NAME LIKE 'Paid[0-9]%'
	)
ORDER BY RIGHT(COLUMN_NAME, 1),
	CASE 
	WHEN COLUMN_NAME LIKE 'Accnt[0-9]%' THEN 1
	WHEN COLUMN_NAME LIKE 'Assessed[0-9]%' THEN 2
	WHEN COLUMN_NAME LIKE 'Paid[0-9]%' THEN 3
	ELSE 999
	END

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 02:12:40

I would advise to have a loot at
http://databases.about.com/od/specificproducts/a/normalization.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources