March 21, 2013 at 5:23 am
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
March 21, 2013 at 5:37 am
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 [/url]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
March 21, 2013 at 5:56 am
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
March 21, 2013 at 6:51 am
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
March 21, 2013 at 9:27 am
Eugene, quite right about the coalesce - it could end up being very messy and involved more that one query.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
March 21, 2013 at 11:27 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 21, 2013 at 4:56 pm
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
March 21, 2013 at 5:14 pm
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
March 21, 2013 at 5:28 pm
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 likeMsg 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) A socialist is someone who will give you the shirt off *someone else's* back.
March 21, 2013 at 7:02 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply