Solved

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

Posted on 2003-11-10
2
714 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
ID: 9719376
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
ID: 9725506
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

828 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