Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


script


script

Author
Message
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
can anyone help me with a script to move some data in the same table.

the data looks like this

id field1 field2 field3 field4
1 a null b null
2 null 9 null 6
3 2 null null 8

want the data to look like this after running script

id field1 field2 field3 field4
1 a b null null
2 9 6 null null
3 2 8 null null

hope i have posted in right place
thanks
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4524 Visits: 4559
Am I right in assuming you want to move all columns to the left as far as possible where the target column is not null?

If so you will want to run an update statement using coalesce.

How far have you got? we might be able to tweak the code you have already tried

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
yes thats correct. To be honest i havent gotten very far . I have never used coalesce update query.
are there any examples about?
Many thanks
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3054 Visits: 5478
frecal (3/21/2013)
yes thats correct. To be honest i havent gotten very far . I have never used coalesce update query.
are there any examples about?
Many thanks


I don't think you will get one here using COALESCE. As you not only want to shift values right, you want to null shifted values (columns) too.
I've tried with COALESCE and found that the logic will be over complecated (actually I failed to get one right, especially if you extend your sample data).
So, I've used a bit different technique:



declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))
insert @thedatalookslikethis
select 1, 'a', null, 'b', null
union select 2, null, '9', null, '6'
union select 3, '2', null, null, '8'
union select 4, 'a', null, null, null
union select 5, null, 'a', null, null
union select 6, null, null, 'a', null
union select 7, null, null, null, 'a'
union select 8, null, null, 'a', 'b'
union select 9, 'a', null, 'b', 'c'
union select 10, 'a', 'b', null, 'c'
union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields
AS
(
SELECT t.id
,MAX(CASE WHEN f=1 THEN v ELSE NULL END) Field1
,MAX(CASE WHEN f=2 THEN v ELSE NULL END) Field2
,MAX(CASE WHEN f=3 THEN v ELSE NULL END) Field3
,MAX(CASE WHEN f=4 THEN v ELSE NULL END) Field4
FROM @thedatalookslikethis t
CROSS APPLY (SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY (CASE WHEN v IS NULL THEN 5 ELSE f END)) f, v
FROM (VALUES (1,field1),(2,field2),(3,field3),(4,field4)) nn(f,v)
ORDER BY (CASE WHEN v IS NULL THEN 5 ELSE f END)) c
GROUP BY t.id
)

UPDATE t
SET Field1 = sf.Field1
,Field2 = sf.Field2
,Field3 = sf.Field3
,Field4 = sf.Field4
FROM @thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4524 Visits: 4559
Eugene, quite right about the coalesce - it could end up being very messy and involved more that one query.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 6698
You can use COALESCE(): for only 4 values, it's actually clearer to me personally (ymmv) than the other method.


SELECT
t.id,
COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,
CASE WHEN t.field1 IS NOT NULL THEN COALESCE(t.field2, t.field3, t.field4)
WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field2_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN (t.field1 IS NOT NULL AND t.field3 IS NOT NULL) OR
(t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4
ELSE NULL END AS field3_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL AND t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field4_new
FROM @thedatalookslikethis t
ORDER BY t.id



SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
Thanks Very much for this i was trying to also get it to work using this method (it is easier to follow). but get an error like
Msg 1033, Level 15, State 1, Line 34
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

should i have just replaced the Select statement?

declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))
insert @thedatalookslikethis
select 1, 'a', null, 'b', null
union select 2, null, '9', null, '6'
union select 3, '2', null, null, '8'
union select 4, 'a', null, null, null
union select 5, null, 'a', null, null
union select 6, null, null, 'a', null
union select 7, null, null, null, 'a'
union select 8, null, null, 'a', 'b'
union select 9, 'a', null, 'b', 'c'
union select 10, 'a', 'b', null, 'c'
union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields
AS
(
SELECT
t.id,
COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,
CASE WHEN t.field1 IS NOT NULL THEN COALESCE(t.field2, t.field3, t.field4)
WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field2_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN (t.field1 IS NOT NULL AND t.field3 IS NOT NULL) OR
(t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4
ELSE NULL END AS field3_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL AND t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field4_new
FROM @thedatalookslikethis t
ORDER BY t.id

)

UPDATE t
SET Field1 = sf.Field1
,Field2 = sf.Field2
,Field3 = sf.Field3
,Field4 = sf.Field4
FROM @thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
Sorry to bother you again. I need to check if the field1= '1' and replace it if it is .
this does but then it puts the 1 in the field 4 ?

thanks so much


;WITH shiftedFields
AS
(
SELECT t.id
,MAX(CASE WHEN f=1 THEN v ELSE NULL END) Field1
,MAX(CASE WHEN f=2 THEN v ELSE NULL END) Field2
,MAX(CASE WHEN f=3 THEN v ELSE NULL END) Field3
,MAX(CASE WHEN f=4 THEN v ELSE NULL END) Field4
FROM thedatalookslikethis t
CROSS APPLY (SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY (CASE WHEN v IS NULL or v = '1' THEN 5 ELSE f END)) f, v
FROM (VALUES (1,field1),(2,field2),(3,field3),(4,field4)) nn(f,v)
ORDER BY (CASE WHEN v IS NULL or v = '1' THEN 5 ELSE f END)) c
GROUP BY t.id
)

UPDATE t
SET Field1 = sf.Field1
,Field2 = sf.Field2
,Field3 = sf.Field3
,Field4 = sf.Field4
FROM thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3957 Visits: 6698
frecal (3/21/2013)
Thanks Very much for this i was trying to also get it to work using this method (it is easier to follow). but get an error like
Msg 1033, Level 15, State 1, Line 34
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

should i have just replaced the Select statement?

declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))
insert @thedatalookslikethis
select 1, 'a', null, 'b', null
union select 2, null, '9', null, '6'
union select 3, '2', null, null, '8'
union select 4, 'a', null, null, null
union select 5, null, 'a', null, null
union select 6, null, null, 'a', null
union select 7, null, null, null, 'a'
union select 8, null, null, 'a', 'b'
union select 9, 'a', null, 'b', 'c'
union select 10, 'a', 'b', null, 'c'
union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields
AS
(
SELECT
t.id,
COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,
CASE WHEN t.field1 IS NOT NULL THEN COALESCE(t.field2, t.field3, t.field4)
WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field2_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN (t.field1 IS NOT NULL AND t.field3 IS NOT NULL) OR
(t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4
ELSE NULL END AS field3_new,
CASE WHEN t.field1 IS NOT NULL AND t.field2 IS NOT NULL AND t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field4_new
FROM @thedatalookslikethis t
--ORDER BY t.id --<<-- DELETE THIS

)

UPDATE t
SET Field1 = sf.field1_new
,Field2 = sf.Field2_new
,Field3 = sf.Field3_new
,Field4 = sf.Field4_new
FROM @thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis



Sorry, just remove the ORDER BY, it's not needed anyway.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
frecal
frecal
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 287
if there is a 1 in field1 i need to treat it as though its a null so i tried this but no luck. can you see what i am missing please.thanks

declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))
insert @thedatalookslikethis
select 1, 'a', null, 'b', null
union select 2, null, '9', null, '6'
union select 3, '2', null, null, '8'
union select 4, 'a', null, null, null
union select 5, '1', 'a', null, null
union select 6, null, null, 'a', null
union select 7, null, null, null, 'a'
union select 8, null, null, 'a', 'b'
union select 9, 'a', null, 'b', 'c'
union select 10, 'a', 'b', null, 'c'
union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields
AS
(
SELECT
t.id,
COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,
CASE WHEN (ISNULL(t.field1, '1') != '1') THEN COALESCE(t.field2, t.field3, t.field4)
WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)
WHEN t.field3 IS NOT NULL THEN t.field4
ELSE NULL END AS field2_new,
CASE WHEN ((ISNULL(t.field1, '1') != '1') AND t.field2 IS NOT NULL) THEN COALESCE(t.field3, t.field4)
WHEN ((ISNULL(t.field1, '1') != '1') AND (t.field3 IS NOT NULL OR
t.field2 IS NOT NULL AND t.field3 IS NOT NULL)) THEN t.field4
ELSE NULL END AS field3_new,
CASE WHEN (ISNULL(t.field1, '1') != '1') AND (t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4
ELSE NULL END AS field4_new
FROM @thedatalookslikethis t


)



UPDATE t
SET Field1 = sf.field1_new
,Field2 = sf.Field2_new
,Field3 = sf.Field3_new
,Field4 = sf.Field4_new
FROM @thedatalookslikethis t
JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search