September 25, 2001 at 1:39 pm
I have a table with the following columns:
CollectionID
UserID
QuestionID
Response
I have a stored procedure that creates a table for a given collection where each Question ID becomes a column, and each user gets a row. What I want to do now is export that data to csv format using a single DTS. The problem is that the columns change depending upon which collection is chosen. How do I get the transoformation script to change along with the output table?
Thanks!
Edited by - acullen on 09/25/2001 1:50:47 PM
September 25, 2001 at 3:27 pm
I'd say step out of DTS for a minute, attack this with plain VB or VBScript. Try to write some code or pseudo code that describes how you would do it. Something like this:
1) Create a table that has columns based on the distinct questionids in your data table - this would be your temp output table - you'd export it.
2) For each unique userid, get all records associated with it and map those into the table from step 1.
Once you get it figured out, then you can move it into a DTS package as an ActiveX script. Much easier to prototype outside of the designer. Give it a shot, post what you come up with, we'll try to help you more from there.
Andy
September 25, 2001 at 3:43 pm
quote:
I'd say step out of DTS for a minute, attack this with plain VB or VBScript. Try to write some code or pseudo code that describes how you would do it.
Actually, I already HAVE a stored procedure that creates the output table - it works like this:
1. Create a temp table that grabs distinct question IDs, then use a cursor on that table to patch together a "create table" statement.
2. I add a record for each user, with all the question fields left as null.
3. I then run through the cursor in step 1 a second time, this time creating an update statement for each question.
I could easily return this monstrous recordset via VB. The question is, how do I move this data into a file that can either be emailed to or downloaded by my users?
Thanks for the help!
September 25, 2001 at 7:24 pm
How about saving it as XML? Once you have the recordset in VB you can call the save method of the recordset with the option to save as xml. You could even take it one step further and send the xml through an XSL transform changing it to just about any format you want.
September 25, 2001 at 7:46 pm
I'd like to see the code if you wouldnt mind posting.
Andy
September 25, 2001 at 9:35 pm
Sure thing. I'll need to strip out some extraneous stuff, and will post ASAP.
September 26, 2001 at 10:00 am
Okay, here's a slimmed down version of the sp. It assumes the existence of a table named tblResponses, with the following columns: clctID int, questionID varchar(10), userID varchar(10), and response varchar(50).
CREATE PROCEDURE spTransformData
@clctID int
AS
DECLARE @SQL nvarchar(4000),
@tblName varchar(50),
@QID varchar(15)
--create the name of the table to be created
SET @tblName = 'tblData' + convert(varchar, @clctID)
--if this table currently exists, drop it
IF (SELECT count(*) FROM sysobjects where name = @tblName)>0
EXEC('DROP TABLE ' + @tblName)
SET @SQL = N'CREATE TABLE ' + @tblName + N' (userID varchar(10)'
DECLARE MakeTbl CURSOR LOCAL FOR
SELECT DISTINCT QuestionID
FROM tblResponses
WHERE clctID=@clctID
--cycle through the questions involved in the collection to add them as columns in the new table
OPEN MakeTbl
FETCH NEXT FROM MakeTbl
INTO @QID
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = @SQL + ', ' + @QID + ' varchar(50)'
FETCH NEXT FROM MakeTbl
INTO @QID
END
CLOSE MakeTbl
SET @SQL= @SQL + ')'
--execute the "CREATE TABLE" command created above
EXEC sp_executesql @SQL
--create & execute T-SQL command to insert a row for each respondent in the new table
SET @SQL = 'INSERT INTO ' + @tblName + ' (userID) SELECT DISTINCT UserID FROM tblResponses WHERE clctID =' + convert(varchar, @clctID)
EXEC sp_executesql @SQL
--NOW, cycle through the cursor again to add user responses to each question
OPEN MakeTbl
FETCH NEXT FROM MakeTbl
INTO @QID
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL= 'UPDATE ' + @tblName + ' SET ' + @QID + ' = r.Response FROM ' +
@tblName + ' t INNER JOIN tblResponses r ON t.userID=r.UserID ' +
'WHERE r.QuestionID= ''' + @QID + ''''
EXEC sp_executesql @SQL
FETCH NEXT FROM MakeTbl
INTO @QID
END
CLOSE MakeTbl
DEALLOCATE MakeTbl
SET @SQL = 'SELECT * FROM ' + @tblName
EXEC sp_executesql @SQL
GO
Edited by - acullen on 09/26/2001 10:02:39 AM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply