August 28, 2008 at 3:13 am
I've a table made up with 7 fields, and I'd like to be able to swap data between them, is this possible?
the table is as follows
date field1 field2 field3 field4 field5 field6
28/08/2008 jim Dave jim jim Dave Dave
29/08/2008 Dave Jim Jim Jim Dave Dave
What I'd like to do is some thing like swap Dave for Jim where date is 28/08/2008.
I thought of useing cursors and temp tables, but I don't seem to be able to get my head round it
August 28, 2008 at 3:30 am
HI there,
Try this:
DECLARE @mytable TABLE
(date DATETIME,
Field1 VARCHAR(10),
Field2 VARCHAR(10),
Field3 VARCHAR(10),
Field4 VARCHAR(10),
Field5 VARCHAR(10),
Field6 VARCHAR(10))
INSERT INTO @mytable
SELECT '2008/08/28','jim','Dave','jim','jim','Dave','Dave' UNION
SELECT '2008/08/29','Dave','Jim','Jim','Jim','Dave','Dave'
SELECT *
FROM @mytable
UPDATE @mytable
SET
Field1 = CASE Field1 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END,
Field2 = CASE Field2 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END,
Field3 = CASE Field3 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END,
Field4 = CASE Field4 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END,
Field5 = CASE Field5 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END,
Field6 = CASE Field6 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' END
WHERE date = '2008/08/28'
SELECT *
FROM @mytable
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 28, 2008 at 3:35 am
Thanks Christopher, The only problem is at the moment there are 92 fields in the database and could grow, that's why I was thinking about using cursors. Is there another way, especially as I might not know the field names at the time of using this
August 28, 2008 at 3:52 am
Do you really mean 92 Fields? or do you mean Rows?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 28, 2008 at 3:54 am
92 fields
August 28, 2008 at 4:21 am
HI Mike,
Well I guess if you know the name of the table then you will probably have to use dynamic SQL to get the column names to build an update statement.
I suppose another good question to ask is what is the actual situation for this problem.
Would it not be possible to simply make sure the data is entered corretly or perhaps another way to solve this issue?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 28, 2008 at 8:44 am
I've finally worked out how to do this in a stored procedure (posted below) the only problem I have is that when I pass in the two strings I'd like to swap within the row if the field it's currently looking at doesn't contain either of the 2 strings it makes that field NULL, anyone have any ideas?
CREATE PROCEDURE spNew_Swap_Areas_by_Date @sFrom as varchar(50), @sTO as varchar(50),@DATE AS VARCHAR(20)
AS
declare @sSQL as nvarchar(4000)
declare @column varchar(128)
declare col_cursor cursor
for
select column_name from information_schema.columns where table_name = 'TI_Work_New' AND COLUMN_NAME <> 'DATE1'
open col_cursor
fetch next from col_cursor into @column
while @@fetch_status = 0
begin
set @ssql = 'update ti_work_new SET [' + @column + '] = CASE [' + @column + '] WHEN ' + '''' + @sFrom + '''' + ' THEN ' + '''' + @sTO + '''' + ' WHEN ' + '''' + @sTO + '''' + ' THEN ' + '''' + @sFrom + '''' + ' END Where [date1] = ''' + @Date + ''' '
exec sp_executesql @ssql
fetch next from col_cursor into @column
end
close col_cursor
deallocate col_cursor
GO
August 28, 2008 at 10:55 am
Mick,
You just need to add an additional where clause that checks the column value equal to either of the passed in values. That would prevent the update statement from updating any rows that do not have one of the values passed into the stored procedure.
August 28, 2008 at 10:57 am
Thanks, I did think along those lines but just couldn't seem to get it right
August 28, 2008 at 2:17 pm
You want it to look like this:
UPDATE @mytable
SET
Field1 = CASE Field1 WHEN 'Jim' THEN 'Dave' WHEN 'Dave' THEN 'Jim' ELSE Field1 END,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 30, 2008 at 7:15 am
many thanks for your solution, I'll try this out when I get into work on Monday
August 30, 2008 at 4:31 pm
Mick,
You've laid out what you want to do quite nicely. I won't get into the lecture on "normalizing" the data but what is the real data and why do you want to do the swap? It might make a difference. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2008 at 2:07 am
Jeff, it's for a problem at work, I'm not the normal DBA hence my lack of any real knowledge, it's just something I play with. I built a toolkit in VB6 which basically allocates up to 96 areas or regions of work to 10 different people, sometime I might want to switch each area between people in cases of leave or illness, the vb code and SP allow me to do this quite quickly
September 1, 2008 at 3:01 pm
Ah... got it... thanks for the feedback.
Still, if the table were properly normalized, this would be a piece of cake. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2008 at 1:48 am
Jeff, how do you mean normalised? I'm interested on your thoughts, although it's probably too late to change what I have now
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply