Geek Answers Handbook

Move cells remaining in sql if left contains null and right contains value

In Sql, I get the result as below. I have 6 columns in total. in the 1st row, the 6th column contains the value, and the second row of the 5th and 6th contains the value. But I need to check each cell, if the previous column does not contain a value, I need to have the first column.

Actual result

abcdef 1 1 2 1 2 3

Expected Result: -

  abcdef 1 1 2 1 2 3

Please write some good answers (I am using SQL Server 2008 R2)

+1
source share
2 answers

Another method similar to @Astander (but using OUTER APPLY instead of PIVOT / UNPIVOT ):

 SELECT a = MIN(CASE WHEN y.rn = 1 THEN y.val END), b = MIN(CASE WHEN y.rn = 2 THEN y.val END), c = MIN(CASE WHEN y.rn = 3 THEN y.val END), d = MIN(CASE WHEN y.rn = 4 THEN y.val END), e = MIN(CASE WHEN y.rn = 5 THEN y.val END), f = MIN(CASE WHEN y.rn = 6 THEN y.val END) FROM t OUTER APPLY ( SELECT x.val, rn = ROW_NUMBER() OVER (ORDER BY rn) FROM ( VALUES (a,1), (b,2), (c,3), (d,4), (e,5), (f,6) ) x (val, rn) WHERE x.val IS NOT NULL ) y GROUP BY t.tid ;

SQL-FIddle Test

+3
source

How about something like

 DECLARE @Table TABLE( a INT, b INT, c INT, d INT, e INT, f INT ) INSERT INTO @Table VALUES (null,null,null,null,null,10), (null,null,null,null,10,20), (null,null,null,10,20,30) SELECT * FROM @Table SELECT SUM([1]) a, SUM([2]) b, SUM([3]) c, SUM([4]) d, SUM([5]) e, SUM([6]) f FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) ValID FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ID FROM @Table ) p UNPIVOT ( val FOR col IN (a,b,c,d,e,f) ) up ) v PIVOT ( SUM(val) FOR ValID IN ([1],[2],[3],[4],[5],[6]) ) p GROUP BY ID

SQL Fiddle DEMO

+1
source

More articles:


All Articles