Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

script Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 5:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #1433728
Posted Thursday, March 21, 2013 5:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 3,013, Visits: 3,210
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
Post #1433737
Posted Thursday, March 21, 2013 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #1433745
Posted Thursday, March 21, 2013 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433758
Posted Thursday, March 21, 2013 9:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 3,013, Visits: 3,210
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
Post #1433864
Posted Thursday, March 21, 2013 11:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1433934
Posted Thursday, March 21, 2013 4:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #1434078
Posted Thursday, March 21, 2013 5:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #1434082
Posted Thursday, March 21, 2013 5:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:31 PM
Points: 1,977, Visits: 2,926
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1434088
Posted Thursday, March 21, 2013 7:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:34 PM
Points: 44, Visits: 263
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
Post #1434104
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse