move cells left in sql if left contains null and right contains value

by

Another way, similar to @Astander’s (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 ;

Test in SQL-FIddle

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  3. T-SQL split string based on delimiter
  4. WHERE Clause vs ON when using JOIN
  5. Partition Function COUNT() OVER possible using DISTINCT
  6. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  7. Cannot use UPDATE with OUTPUT clause when a trigger is on the table
  8. CASE WHEN statement for ORDER BY clause
  9. How to get last 7 days data from current datetime to last 7 days in sql server
  10. Varchar variable is not working in WHERE clause
  11. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  12. Reference an alias elsewhere in the SELECT list
  13. Count Returning blank instead of 0
  14. How to Split String by Character into Separate Columns in SQL Server
  15. Using IF ELSE statement based on Count to execute different Insert statements
  16. How to find out what is locking my tables?
  17. Passing dynamic order by in stored procedure
  18. Select SQL Server database size
  19. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  20. How do I find the data directory for a SQL Server instance?
  21. How to return default value from SQL query
  22. How to concatenate all columns in a select with SQL Server
  23. T-SQL calculate moving average
  24. How to automatically generate unique id in SQL like UID12345678?
  25. Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate row. And show that duplicate rows once [closed]
  26. Dynamic Pivot in Oracle’s SQL
  27. Return 0 if field is null in MySQL
  28. MySQL “between” clause not inclusive?
  29. Is there a quick way to check if ANY column is NULL?
  30. Subtract hours from the now() function

Leave a Comment