Update Cross-Tab query - 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)

Update Cross-Tab query

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2002-02-11 : 15:20:38

I need to create a cross-tab query that is updateable, is this possible?

I'm currently doing this by creating tempory tables updating them and them and then updating the main table before dropping the temp table. This works but it seems there has to be a better way.

Thanks,
TP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-11 : 21:28:58

quote:
This works but it seems there has to be a better way.

Not really. Cross-tabs are not editable data structures, they are transformations of data for the purposes of compact presentation. If you really wanted to edit the data in this format, you should design the tables to match that cross-tab structure.

In any case, if your cross-tab query is using GROUP BY, you cannot update it. If the summary function is anything other than Min or Max, changing the value is meaningless. You'll have to stick with the temp table solution.

How exactly are these values being updated? If you are putting these results into a client app (VB? ASP? ???), then you can create a stored procedure to take the row value and column name/value and update it using something like this:

CREATE PROCEDURE UpdateCross @rowvalue varchar(1000), @colname sysname, @newValue varchar(100) AS
UPDATE normalTable SET pivotValueColumn=@newValue
WHERE rowHeadingColumn=@rowvalue AND pivotColumn=@colname

As the user edits each cell of the cross-tab, the client app would fire this procedure with the row, column, and value. This is definitely viable, but not in a linked-table immediate-update scenario.

Togaspoon
Starting Member

42 Posts

Posted - 2002-02-12 : 12:01:19

quote:
How exactly are these values being updated? If you are putting these results into a client app (VB? ASP? ???), then you can create a stored procedure to take the row value and column name/value and update it using something like this:

This is exactly what I'm doing, I'll give the stored procedure route a go.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources