How to use Coalesce to shift data left
I have the following view that generates the following output. As you can infer from the output,
this view produces a pivoted output.
USI GFP Workstream Review1 Status1 Review2 Status2 Review3 Status3 Review4 Status4
20 A AA NULL NULL B Be C OR D NOT
24 A vc v OR NULL NULL C OR D NOT
I now need to take this output and shift it leftward by eliminating all nulls as shown below
USI GFP Workstream Review1 Status1 Review2 Status2 Review3 Status3 Review4 Status4
20 A AA B Be C OR D NOT
24 A vc v OR C OR D NOT
As you can see, the record for USI 20 had a null value for review1, consequently, All the values were shifted left
USING the following code
SELECT USI, GFP, Workstream,
Coalesce(Review1, Review2, Review3, Review4, Review5, Review6, Review7, Review8, Review9, Review10) AS Review1
shifted the first non null value to Review1, I'm trying to figure out out to do the same for the remainder off the reviews
I've attached a text file which repeats the above sample with proper spacing
this view produces a pivoted output.
USI GFP Workstream Review1 Status1 Review2 Status2 Review3 Status3 Review4 Status4
20 A AA NULL NULL B Be C OR D NOT
24 A vc v OR NULL NULL C OR D NOT
I now need to take this output and shift it leftward by eliminating all nulls as shown below
USI GFP Workstream Review1 Status1 Review2 Status2 Review3 Status3 Review4 Status4
20 A AA B Be C OR D NOT
24 A vc v OR C OR D NOT
As you can see, the record for USI 20 had a null value for review1, consequently, All the values were shifted left
USING the following code
SELECT USI, GFP, Workstream,
Coalesce(Review1, Review2, Review3, Review4, Review5, Review6, Review7, Review8, Review9, Review10) AS Review1
shifted the first non null value to Review1, I'm trying to figure out out to do the same for the remainder off the reviews
I've attached a text file which repeats the above sample with proper spacing
ASKER
Hello,
I've already tried the following
COALESCE(Review1, Review2, Review3, Review4) AS Review1
COALESCE(Review2, Review3, Review4) AS Review2
The problem is that there are 8 reviews, either one or all can have null values. I've been thinkng about what I want to accomplish in order to better formualate the question.
I'd like to use coalasce to return subseqent non-null values. For example, if the pivot output is
Review1 Review2 Review3 Review4
XX NULL NULL XX
I would like to generate an output of
Review1 Review2 Review3 Review4
XX XX
I've already tried the following
COALESCE(Review1, Review2, Review3, Review4) AS Review1
COALESCE(Review2, Review3, Review4) AS Review2
The problem is that there are 8 reviews, either one or all can have null values. I've been thinkng about what I want to accomplish in order to better formualate the question.
I'd like to use coalasce to return subseqent non-null values. For example, if the pivot output is
Review1 Review2 Review3 Review4
XX NULL NULL XX
I would like to generate an output of
Review1 Review2 Review3 Review4
XX XX
something like this maybe:
with t as (
select 20 USI,'A' GFP,'AA' Workstream,NULL Review1,NULL Status1,'B' Review2,'Be' Status2, 'C' Review3, 'OR' Status3,'D' Review4,'NOT' Status4
union select 24, 'A', 'vc', 'v', 'OR', NULL, NULL, 'C', 'OR', 'D', 'NOT'
)
select USI, GFP,Workstream,
Coalesce(Review1,Review2,Review3,Review4) Review1, Coalesce(Status1,Status2,Status3,Status4) Status1,
Coalesce(Review2,Review3,Review4) Review2, Coalesce(Status2,Status3,Status4) Status2,
Coalesce(Review3,Review4) Review3, Coalesce(Status3,Status4) Status3,
(case when (Review1 is null or Review2 is null or Review3 is null) then null else Review4 end) Review4,
(case when (Review1 is null or Review2 is null or Review3 is null) then null else Status4 end) Status4
from t
USI GFP Workstream Review1 Status1 Review2 Status2 Review3 Status3 Review4 Status4
20 A AA B Be B Be C OR NULL NULL
24 A vc v OR C OR C OR NULL NULL
above does not work fine for multiple null values... thinking something else...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello HainKurt,
The function you posted worked great. However, I am having trouble understanding how the code work. I think that I understand the Row_Number function. Basically what that is doing is addingg a row number based on what is in the Over(Order by ) clause. This rownumber is assigned the alias of rn
You are then returning the top row number and the associated value of the Review(in the example given) for that row. I need to study it more in order to understand it
The function you posted worked great. However, I am having trouble understanding how the code work. I think that I understand the Row_Number function. Basically what that is doing is addingg a row number based on what is in the Over(Order by ) clause. This rownumber is assigned the alias of rn
You are then returning the top row number and the associated value of the Review(in the example given) for that row. I need to study it more in order to understand it
ASKER
Thanks again. It works great. I'm reading up on row_number and rank right now.
first I am selecting all by giving each param as a number
1 null
2 something
3 something else
4 null
5 another
then i get rid of null values
2 something
3 something else
5 another
then I add a row number, based on the indexes
1 2 something
2 3 something else
3 5 another
then select the desired row passed as parameter... if i say 3, i get third row from final result...
1 null
2 something
3 something else
4 null
5 another
then i get rid of null values
2 something
3 something else
5 another
then I add a row number, based on the indexes
1 2 something
2 3 something else
3 5 another
then select the desired row passed as parameter... if i say 3, i get third row from final result...
COALESCE(Review1, Review2, Review3, Review4) AS Review1
Then, keep removing the left-most parameter.
COALESCE(Review2, Review3, Review4) AS Review2
i.e., start with the column you are trying to display and include EVERY column that can be a replacement afterwards.
You may get things out of order with status (if it is not NULL in the same places), but it at least should get you started with COALESCE(). Another approach with SQL 2005 or higher is to UNPIVOT the data and then PIVOT based on ranking (sequence of the ordinal value of the column).