Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-10
2
Medium Priority
?
745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 58

Accepted Solution

by:
amit_g earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 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