move cells left in sql if left contains null and right contains value
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
Move cells Left if left column contains null and right column is not null
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
Hana sql move cells left in sql if left contains null and right contains value
Hmmm . . . In Hana, I think you can unpivot using union all, then row_number() and aggregation:
select id,
max(case when order = 1 then val end) as a,
max(case when order = 2 then val end) as b,
. . .
from (select id, val, row_number() over (partition by id rder by ord) as as
from ((select 1 as ord, id, a as val from t) union all
(select 2 as ord, id, b as val from t) union all
. . .
) t
where val is not null
) t
group by id;
Here id refers to the columns or groups of columns that uniquely specify each row.
SQL: Shift columns value to left if a NULL value
You can use a lateral join, filter, and reaggregate:
select *
from categories c cross join lateral
(select max(case when seqnum = 1 then val end) as l1,
max(case when seqnum = 2 then val end) as l2,
max(case when seqnum = 3 then val end) as l3,
max(case when seqnum = 4 then val end) as l4
from (select v.*, row_number() over (order by ord) as seqnum
from (values (1, l1), (2, l2), (3, l3), (4, L4)
) v(ord, val)
where val is not null
) v
) v;
Here is a db<>fiddle using Postgres.
Or using arrays:
select (array_remove(array[l1, l2, l3, l4], NULL))[1] as l1,
(array_remove(array[l1, l2, l3, l4], NULL))[2] as l2,
(array_remove(array[l1, l2, l3, l4], NULL))[3] as l3,
(array_remove(array[l1, l2, l3, l4], NULL))[4] as l4
from categories c;
I'm not sure which of these is easier for you to translate into Snowflake.
MS SQL 2012 : In SQL Shift columns to left side if column contains 0
This should do what you need (demo)
SELECT i.cust_id,
oa.*
FROM input_table i
OUTER APPLY (SELECT pvt.*
FROM (SELECT month,
col = CONCAT('month', ROW_NUMBER() OVER (ORDER BY idx))
FROM (SELECT month,
idx,
to_preserve = MAX(IIF(month=0,0,1)) OVER (ORDER BY idx)
FROM (VALUES (1, month1),
(2, month2),
(3, month3),
(4, month4),
(5, month5) ) V(idx, month)) unpvt
WHERE to_preserve = 1) t
PIVOT (MAX(month) FOR col IN (month1, month2, month3, month4, month5)) pvt
) oa
It unpivots the column values a row at a time.
For example C3 will end up unpivoted to
+---------+-------+-----+-------------+
| cust_id | month | idx | to_preserve |
+---------+-------+-----+-------------+
| c3 | 0 | 1 | 0 |
| c3 | 0 | 2 | 0 |
| c3 | 100 | 3 | 1 |
| c3 | 0 | 4 | 1 |
| c3 | 0 | 5 | 1 |
+---------+-------+-----+-------------+
The MAX(IIF(month=0,0,1)) OVER (ORDER BY idx) expression ensures all values from the first non zero one onwards have to_preserve set to 1.
Then it selects the values with the to_preserve flag and uses ROW_NUMBER to provide a value that can be used for pivoting into the correct new column.
How can omit Null values in columns and shift/replace omited values by right values (shift columns to left) in sql
It looks like you are using SQL Server. If so, you are in luck because a lateral join is just what you need:
select v.*
from t cross apply
(select max(case when seqnum = 1 then col end) as t1,
max(case when seqnum = 2 then col end) as t2,
max(case when seqnum = 3 then col end) as t3,
max(case when seqnum = 4 then col end) as t4,
max(case when seqnum = 5 then col end) as t5,
max(case when seqnum = 6 then col end) as t6
from (select col, row_number() over (order by ind) as seqnum
from (values (1, T1), (2, T2), (3, T3), (4, T4), (5, T5), (6, T6)
) v(ind, col)
where col <> ''
) v
) v;
Can I move SQL Server data along one column to the left if previous column cell is NULL?
This doesn't quite give the answer you were after, but I believe that's because SW02 has PC twice. I believe it should have only had it once. This gives:
CREATE TABLE #Policy (Pol_ref varchar(10),
Pol_mix varchar(10),
Pol_1_Type char(2),
Pol_2_Type char(2),
Pol_3_Type char(2),
Pol_4_Type char(2));
INSERT INTO #Policy
VALUES ('XXXXXXSW01','Car',NULL,'PC',NULL,NULL),
('XXXXXXSW02','Modern',NULL,'PC','MB',NULL),
('XXXXXXSW01','Car',NULL,NULL,'PC',NULL),
('XXXXXXSW03','Modern','PC',NULL,'MB',NULL);
GO
SELECT *
FROM #Policy;
GO
SELECT Pol_ref,
Pol_mix,
COALESCE(Pol_1_Type,Pol_2_Type,Pol_3_Type,Pol_4_Type) AS Pol_1_Type,
CASE WHEN Pol_1_Type IS NOT NULL THEN COALESCE(Pol_2_Type,Pol_3_Type,Pol_4_Type)
ELSE COALESCE(Pol_3_Type,Pol_4_Type)
END AS Pol_2_Type,
CASE WHEN Pol_1_Type IS NOT NULL AND Pol_2_Type IS NOT NULL THEN COALESCE(Pol_3_Type,Pol_4_Type)
WHEN Pol_1_Type IS NOT NULL AND Pol_2_Type IS NULL THEN Pol_4_Type
WHEN Pol_2_Type IS NOT NULL AND Pol_1_Type IS NULL THEN Pol_4_Type
END AS Pol_3_Type,
CASE WHEN Pol_1_Type IS NOT NULL AND Pol_2_Type IS NOT NULL AND Pol_3_Type IS NOT NULL THEN Pol_4_Type END AS Pol_4_Type
FROM #Policy
GO
DROP TABLE #Policy;
GO
SQL moving data along columns
PIVOT and UNPIVOT will be able to do the job. The idea:
UNPIVOTto get the data into rows- Clean out empty rows and calculate what the new column will be
PIVOTto get the cleaned data back into columns.
Here's one way of doing it using a bunch of CTEs in one statement. Note I've assumed there is an ID column and I've made up the table name:
;WITH Unpivoted AS
(
-- our data into rows
SELECT ID, TelField, Tel
FROM Telephone
UNPIVOT
(
Tel FOR TelField IN (TEL01,TEL02,TEL03,TEL04,TEL05,TEL06)
) as up
),
Cleaned AS
(
-- cleaning the empty rows
SELECT
'TEL0' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TelField) AS VARCHAR) [NewTelField],
ID,
TelField,
Tel
FROM Unpivoted
WHERE NULLIF(NULLIF(Tel, ''), 'n/a') IS NOT NULL
),
Pivoted AS
(
-- pivoting back into columns
SELECT ID, TEL01, TEL02, TEL03
FROM
(
SELECT ID, NewTelField, Tel
FROM Cleaned
) t
PIVOT
(
-- simply add ", TEL04, TEL05, TEL06" if you want to still see the
-- other columns (or if you will have more than 3 final telephone numbers)
MIN(Tel) FOR NewTelField IN (TEL01, TEL02, TEL03)
) pvt
)
SELECT * FROM Pivoted
ORDER BY ID
That will shift the telephone numbers into their correct place in one go. You can also change the Pivoted in SELECT * FROM Pivoted to any of the other CTEs - Unpivoted, Cleaned - to see what the partial results would look like. Final result:
Related Topics
The Conversion of a Datetime2 Data Type to a Datetime Data Type Resulted in an Out-Of-Range
Extbase - Get Created SQL from Query
Difference Between Timestamps in Milliseconds in Oracle
How to Grant Myself Admin Access to a Local SQL Server Instance
SQL Join on Multiple Columns in Same Tables
Pl/SQL Block Problem: No Data Found Error
Try_Convert for SQL Server 2008 R2
Prevent Duplicate Values in Left Join
Table-Valued Function - Order by Is Ignored in Output
In SQL How to Get the Maximum Value for an Integer
Combine Multiple Select Statements
Get SQL Xml Attribute Value Using Variable
Why Does the Wm_Concat Not Work Here
Why Are Foreign Keys More Used in Theory Than in Practice
How to Execute a Ms SQL Server Stored Procedure in Java/Jsp, Returning Table Data