Solved

Problems denormalizing many-to-many join table into single table

Posted on 2003-11-10
2
682 Views
Last Modified: 2010-08-05
I have to transform a standard relational tableset into an single unrelational table (our partner's lazy way of making the web db, grr, can't change that now).  I'm having difficulty with one part of the munging.

There's is a Projects table and a simple ProjectTypes lookup table.  There is also a Projects_ProjectTypes table that has just the two primary keys, creating a many-to-many join table.  

For each ProjectType, there needs to be a corresponding column in the NewProjects table.  So if ProjectTypeID=3, the new table's column would be "_ptype_3", and I need to put a '1' there if that project type applies to a particular project.

(I have to do this for several other similar join tables too, once this one is worked out.)

I thought I had it with the query below. Unfortunately, in in Query Analyzer, the UPDATE command errors out with "Invalid column name '@theField'".

How do I de-normalize these tables to the format I need?  Is there an easier way?

-- -------------------------
-- FYI, pid and ptypeid refer to ProjectID and ProjectTypeIDs
declare @pid INT
declare @ptypeID INT
declare @currID int
declare @numRows int
declare @theField VARCHAR(50)

SELECT @numRows = Count(*) FROM Projects_ProjectTypes

set @currID = 1

WHILE (@currID <= @numRows)
BEGIN
      SELECT @pid = ProjectID, @ptypeID = ProjectTypeID FROM Projects_ProjectTypes WHERE ID = @currID

      set @theField = '_ptype_' + cast(@ptypeID as varchar(4))

      print 'PID=' + cast(@pid as varchar(4)) + ', ' + @theField + '=1'

      UPDATE NewProjects SET [@theField] = '1' WHERE _id = @pid

      SET @currID = @currID + 1
      
END
GO

--
--SELECT _id, _ptype_1, _ptype_2, _ptype_3, _ptype_4, _ptype_5 FROM NewProjects;
--
-- The above query should return something like:
--
-- ID   _ptype_1   _ptype_2   _ptype_3   _ptype_4   _ptype_5  
-- ---------------------------------------------------------------------
-- 23       1                                1             1
-- 24                       1                1


0
Comment
Question by:gpupurs
2 Comments
 
LVL 58

Accepted Solution

by:
amit_g earned 250 total points
Comment Utility
Run this script in query analyser. This will give you another script that you can run to insert data in the newProjects Table.

declare @ProjectTypeID as varchar(4)
declare @Select as varchar(2000)
declare @SQL as varchar(2000)

declare ProjectTypes_Cursor cursor for select * from ProjectTypes

OPEN ProjectTypes_Cursor

set @Select = ''
set @SQL = ''

FETCH NEXT FROM ProjectTypes_Cursor into @ProjectTypeID

WHILE @@FETCH_STATUS = 0
BEGIN
      set @Select = @Select + ' ,PPT_' + @ProjectTypeID + '.ProjectID / PPT_' + @ProjectTypeID + '.ProjectID as _ptype_' + @ProjectTypeID + char(10)

      set @SQL = @SQL + ' left outer join Projects_ProjectTypes PPT_' + @ProjectTypeID + char(10)
      set @SQL = @SQL + ' on P.ProjectID = PPT_' + @ProjectTypeID + '.ProjectID'
      set @SQL = @SQL + ' and PPT_' + @ProjectTypeID + '.ProjectTypeID = ' + @ProjectTypeID + char(10)

      FETCH NEXT FROM ProjectTypes_Cursor into @ProjectTypeID
END

set @Select = ' select P.ProjectId' + char(10) + @Select + ' from Projects P' + char(10)
set @SQL = @Select + @SQL

print @SQL

CLOSE ProjectTypes_Cursor
DEALLOCATE ProjectTypes_Cursor
0
 

Author Comment

by:gpupurs
Comment Utility
Thanks, this was the starting point I needed!.  Clever way to get the '1' values in there, by dividing the ProjectID by itself.

Your code had two slight errors:
"Cursorfetch: The number of variables declared in the INTO list must match that of selected columns. select P.ProjectId from Projects P"

But I fixed it by changing the ProjectTypes_Cursor declaration from "*" to "ProjectTypeID".

In the last "set @Select" command I changed "Projects" to "Projects_ProjectTypes".

Since I had to run this code on several other join tables, I've generalized it with variables for table and field names.  I'm pasting here for future searchers, perhaps it will come in handy for someone!

Take the output from this script, paste into another Query Analyzer window, and run it.  

Gotchas to be aware of:
- This assumes INT data type for the table IDs.
- If you have A LOT of fields, 8000 characters for the generated SQL will not be long enough. You could try renaming tables/fields to as few characters as possible.

---------------------------------------
declare @JoinTableID as varchar(10)
declare @Select as varchar(8000)
declare @SQL as varchar(8000)
declare @CreateTable as varchar(8000)
declare @CreateTableSQL as varchar(8000)
declare @Update as varchar(8000)
declare @UpdateSQL as varchar(5000)
declare @JoinTableName as varchar(50)
declare @OldPrimaryKey as varchar(50)
declare @OldFieldName as varchar(50)
declare @NewTableName as varchar(50)
declare @NewPrimaryKey as varchar(50)
declare @NewFieldName as varchar(50)

-- //////////////////////////////////////
--
--  THESE DEFINITIONS MUST BE CUSTOMIZED!!
--
set @JoinTableName = 'Projects_ProjectTypes'
set @OldPrimaryKey = 'ProjectID'
set @OldFieldName = 'ProjectTypeID'
declare JoinTable_Cursor CURSOR FOR SELECT DISTINCT ProjectTypeID FROM Projects_ProjectTypes
set @NewTableName = 'NewProjects'
set @NewPrimaryKey = 'ID'
set @NewFieldName = 'projectType_'
--
-- //////////////////////////////////////

OPEN JoinTable_Cursor

set @Select = ''
set @SQL = ''
set @CreateTable = ''
set @CreateTableSQL = ''
set @Update = ''
set @UpdateSQL = ''

FETCH NEXT FROM JoinTable_Cursor into @JoinTableID

WHILE @@FETCH_STATUS = 0
BEGIN
     set @CreateTable = @CreateTable + ' ,' + @NewFieldName + @JoinTableID + ' CHAR(2)' + char(10)

     set @Select = @Select + ' ,JT_' + @JoinTableID + '.' + @OldPrimaryKey + ' / JT_' + @JoinTableID + '.' + @OldPrimaryKey + ' as ' + @NewFieldName + @JoinTableID + char(10)
     set @SQL = @SQL + ' left outer join ' + @JoinTableName + ' JT_' + @JoinTableID + char(10)
     set @SQL = @SQL + ' on OLD.' + @OldPrimaryKey + ' = JT_' + @JoinTableID + '.' + @OldPrimaryKey
     set @SQL = @SQL + ' and JT_' + @JoinTableID + '.' + @OldFieldName + ' = ' + @JoinTableID + char(10)

     set @Update = @Update + ' ,' + @NewFieldName + @JoinTableID + ' = #tempData.' + @NewFieldName + @JoinTableID + char(10)

     FETCH NEXT FROM JoinTable_Cursor into @JoinTableID
END


set @CreateTableSQL = 'CREATE TABLE #tempData (' + char(10) + @NewPrimaryKey + ' INT' + char(10) + @CreateTable + ')' + char(10)

set @Select = ' SELECT OLD.' + @OldPrimaryKey + ' as ' + @NewPrimaryKey + char(10) + @Select + ' FROM ' + @JoinTableName + ' OLD' + char(10)
set @SQL = 'INSERT INTO #tempData ' + char(10) + @Select + @SQL

set @UpdateSQL = 'UPDATE ' + @NewTableName + ' SET ' + char(10)
set @UpdateSQL = @UpdateSQL + @NewPrimaryKey + ' = ' + @NewTableName + '.' + @NewPrimaryKey + char(10) + @Update
set @UpdateSQL = @UpdateSQL + 'FROM #tempData WHERE #tempData.' + @NewPrimaryKey + ' = ' + @NewTableName + '.' + @NewPrimaryKey


print 'IF EXISTS(SELECT Name FROM tempdb..sysobjects WHERE Name LIKE ''#tempData%'')'
print 'DROP TABLE #tempData' + char(10) + 'GO' + char(10)

print @CreateTableSQL
print @SQL
print @UpdateSQL


CLOSE JoinTable_Cursor
DEALLOCATE JoinTable_Cursor
---------------------------------------
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now