Solved

How to use Coalesce to shift data left

Posted on 2012-03-30
8
351 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37788581
You can do something like this:

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).
0
 

Author Comment

by:chtullu135
ID: 37788881
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
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 37788949
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

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 56

Expert Comment

by:HainKurt
ID: 37788963
above does not work fine for multiple null values... thinking something else...
0
 
LVL 56

Accepted Solution

by:
HainKurt earned 500 total points
ID: 37788997
ok, what about a function like this

createfunction getNNonNullValue(@n int, @v1 varchar(max),@v2 varchar(max),@v3 varchar(max),@v4 varchar(max),@v5 varchar(max),@v6 varchar(max),@v7 varchar(max),@v8 varchar(max)) returns varchar(max) as
begin
  return (select v from (
  select row_number() over (order by n) rn, v from (
  select 1 n, @v1 v
  union select 2, @v2
  union select 3, @v3 
  union select 4, @v4 
  union select 5, @v5 
  union select 6, @v6 
  union select 7, @v7 
  union select 8, @v8
  ) x where v is not null
  ) xx where rn=@n
  )
end;

select dbo.getNNonNullValue(3,'A',null,null,'B',null,'C','D',null)
C

Open in new window


and use it in your query like:

getNNonNullValue(1, Review1, Review2,..., Review8) as Review1,
getNNonNullValue(1, Status1, Status2,..., Status8) as Status1,
getNNonNullValue(2, Review1, Review2,..., Review8) as Review2,
getNNonNullValue(2, Status1, Status2,..., Status8) as Status2,
...
getNNonNullValue(8, Review1, Review2,..., Review8) as Review8,
getNNonNullValue(8, Status1, Status2,..., Status8) as Status8,
0
 

Author Comment

by:chtullu135
ID: 37796672
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
0
 

Author Closing Comment

by:chtullu135
ID: 37798036
Thanks again.  It works great.  I'm reading up on row_number and rank right now.
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 37826044
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...
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question