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

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


gpupursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gpupursAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.