Dynamically transpose & export data to csv

  • 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

  • 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

  • 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!

  • 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.

  • I'd like to see the code if you wouldnt mind posting.

    Andy

  • Sure thing. I'll need to strip out some extraneous stuff, and will post ASAP.

  • 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