Dynamicly creating columns - SQL Server Forums

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

SQL Server Development (2000)

Dynamicly creating columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-30 : 08:50:25

Southpaw writes "Hi there. Let me just start by saying that your site is a great resource and that you've made my life as a developer a hell of a lot easier.

Now...I'm making a user registration module for our web content management system. One feature that it has is to print out a report (a table) of all registered users along with which interests they've picked out. The interests however are not static.

Here's a fairly simplified way of how I've done it.
I have three tables (that are of relevance):
ru_users, ru_interests and ru_interests_on_users.
ru_users has two columns: user_id and user_name.
ru_interests has two columns: interest_id and interest_name.
ru_interests_on_users has two columns: user_id and interest_id.

As you can see I use a linking table (ru_interests_on_users) because the relationship between users and interests is a many-to-many relationship.

Now, here's the stored procedure I wrote for generating the report:
CREATE PROCEDURE sp_GenerateRUUserReport AS

-- Turn NoCount on
SET NOCOUNT ON

-- Create a temporary table for the report
CREATE TABLE #user_report (
    user_id INT,
    user_name VARCHAR(64))

-- Populate the temporary report table with all the users
INSERT INTO #user_report (
    user_id,
    user_name)
SELECT
    user_id,
    user_name
FROM ru_users

-- Declare variables
DECLARE @sql VARCHAR(500), @interest_id INT

-- Build a cursor to loop through all the interests
DECLARE interest_cursor CURSOR LOCAL FOR
SELECT interest_id
FROM ru_interests
ORDER BY interest_name

-- Open the cursor
OPEN interest_cursor

-- Get first record
FETCH NEXT FROM interest_cursor
INTO @interest_id

-- Iterate through the rows in the ru_interests table
WHILE @@FETCH_STATUS = 0
BEGIN -- (Loop)

    -- Dynamicly add a BIT column to the temporary report table (one column for each interest)
    SET @sql = 'ALTER TABLE #user_report ADD interest_' + CAST(@interest_id AS VARCHAR) + ' BIT DEFAULT 0 NOT NULL'
    EXECUTE(@sql)

    -- Now set the BIT to 1 for the users that have selected this particular interest
    SET @sql = 'UPDATE #user_report SET interest_' + CAST(@interest_id AS VARCHAR) + ' = 1 WHERE user_id IN (SELECT user_id FROM ru_interests_on_users WHERE interest_id = ' + CAST(@interest_id AS VARCHAR) + ')'
    EXECUTE(@sql)

-- Get next record
    FETCH NEXT FROM interest_cursor
    INTO @interest_id
END -- (Next)

-- Close and deallocate the cursor (close and destroy)
CLOSE interest_cursor
DEALLOCATE interest_cursor

-- Return the report by selecting all columns from the temporary report table
SELECT * FROM #user_report
ORDER BY user_name

-- Destroy temporary report table
DROP TABLE #user_report

-- Turn NoCount off
SET NOCOUNT OFF

Is there any better (ie. faster) way to do this on MSSQL 7?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources