How to remove columns that are empty or that have duplicate values
-
This is more of a how-to, but i would like input from anyone who can think of a better way to write the query. We have a list of items that have 10 landed costs columns. We've found recently that we have issues adding items to our POs when the landed cost columns have NULL gaps or duplicates, i.e. LC1 = AR25, LC2 = MN13, LC3 = NULL, LC4 = FRE, LC5 = FRE
Basically what I'm doing is using cross apply to select a distinct list of the landed costs without NULL values and adding row numbers, then pivoting them back into columns.
update imitmidx_sql
set
[landed_cost_cd] = lcc.[1]
,[landed_cost_cd_2] = lcc.[2]
,[landed_cost_cd_3] = lcc.[3]
,[landed_cost_cd_4] = lcc.[4]
,[landed_cost_cd_5] = lcc.[5]
,[landed_cost_cd_6] = lcc.[6]
,[landed_cost_cd_7] = lcc.[7]
,[landed_cost_cd_8] = lcc.[8]
,[landed_cost_cd_9] = lcc.[9]
,[landed_cost_cd_10] = lcc.[10]
from imitmidx_sql imix
cross apply(
-- pivot distinct landed costs back into columns
select piv.*
from (
-- inner query to get distinct list of landed cost codes with row numbers
select distinct
x.lcc_code
,ROW_NUMBER() over (partition by (select 0) order by x.id) rownum
from
(
values
(1, imix.landed_cost_cd)
,(2, imix.landed_cost_cd_2)
,(3, imix.landed_cost_cd_3)
,(4, imix.landed_cost_cd_4)
,(5, imix.landed_cost_cd_5)
,(6, imix.landed_cost_cd_6)
,(7, imix.landed_cost_cd_7)
,(8, imix.landed_cost_cd_8)
,(9, imix.landed_cost_cd_9)
,(10, imix.landed_cost_cd_10)
) x(id, lcc_code)
where x.lcc_code is not null
) src
pivot
(
max(lcc_code) for rownum in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) piv
) lcc -
This would be infinitely easier if you normalized your table structure first. Repeating groups is the first thing you remove when normalizing a table.
-
If I understand your question correctly, you can't. SQL Server enforces first normal form which requires that all records have the same shape. That means that all records have to have the same number of landed costs even if that means that some of the records have null values.
That being said, maybe you should just leave your table unpivoted.
If that doesn't help, then can you please post sample data and expected results. Yes, we do need BOTH. Instructions are in the first link in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The first result is the record before the update. The second result is the record afterwards.
Also, i can't normalize the table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply