sql convert vertical table to horizontal (deep to

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

Transact-SQL (2008)

sql convert vertical table to horizontal (deep to

Author

Topic

mtlondon
Starting Member

2 Posts

Posted - 2010-10-28 : 12:13:30

Hi

I have 2 tables, where one holds Name and address information in a wide format, second table is a deep version where i have the records with field names (i.e. Date Of Birth) in the value and then another field with the actual value.

I need to merge both tables together to create a new 3rd table and have everything in a wide format, so name and address for one record plus another new column with Date of Birth, so in the end i would just have one row for a person.

i am using sql 2008, i dont have any examples to show, which makes it difficult to explain.

a visual example would be

table 1

id name address1 address2 postalcode

100 john 1 anystreet anywhere 125254

table 2

id fieldname fieldvalue

100 dateofbirth 1 jan 1900

100 age 10

100 mobile 123456789

new table should look like

table 3

id name address1 address2 postalcode dateofbirth age mobile

100 john 1 anystreet anywhere 125254 1 jan 1900 10 123456789

i have a very large dataset so need something generic and fast as there are lots of different field names so i would need to add this to the new table as well.

I am trying to run the query over millions of records, we have 1TB space, but this query seems to take up all the space and crashes out claiming it could not allocate space.

can someone please help?

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 05:23:26

Is it crashing because you are making a mistake and getting a cartesian product?
You might consider creating many tables then creating the new table from that.

If that's not feasible then create the new table and add ids in batches.
You also have the issue about column types - I take it your generic table has a character column for the values?

select distinct fieldname into #fldname from tbl2

select 'select id, [' + fieldname + '] = fieldvalue into [tbl1_' + fieldname + '] from tbl2 where fieldname = ''' + fieldname + '''
from #fldname

that should create a lot of statements of the form
select id, [dateofbirth] into [tbl1_dateofbirth] from tbl2 where fieldname = 'dateofbirth'

(could make these temp tables - especially if the database uses full recovery model - or create a new work database for them)

You can now run all these to create tables of the data.
Now all that remains is to combine into a single table. You will need indexes on the individual tables

select 'create unique clustered index ix on [tbl1_' + fieldname + '] (id)'
from #fldname

Run the result to create the indexes

Now just remains to combine all the tables for which you need to query of the form

select tbl1.*, [tbl1_dateofbirth].[dateofbirth], [tbl1_age].[age]
from tbl1
left join [tbl1_dateofbirth]
on tbl1.id = [tbl1_dateofbirth].id
left join [tbl1_age]
on tbl1.id = [tbl1_age].id
....

How many columns do you have - can you hard code this query or do you you need to generate it?
Also if there are two many rows you might want to batch this up using the id. Especially if you want to create the table first and run inserts instead of a select into.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources