View creates multiple rows - need to condense - help

[lymi6977]

I am hoping you can help me.

When you look at the view below, do you know why multiple rows are being created, instead of the fields displaying in one row?
Could it be because of the “else null” in the View script?
Do I need to group something?
I just don’t know how to get this done.

ALTER View [dbo].[vwCIFMaintAddressLines1Or2] as
SELECT CAST(JHMCIF as Varchar(15)) as CIF#, dbo.JHAJulianToDate(JHMDTM) as MaintDate,
JHMTIM as TimeChanged,
CASE WHEN JHMFLD = 'Address line 1' THEN JHMOLD ELSE NULL END as OldAddress1,
CASE WHEN JHMFLD = 'Address line 1' THEN JHMNEW ELSE NULL END as NewAddress1,
CASE WHEN JHMFLD = 'Address line 2' THEN JHMOLD ELSE NULL END as OldAddress2,
CASE WHEN JHMFLD = 'Address line 2' THEN JHMNEW ELSE NULL END as NewAddress2,
CASE WHEN JHMFLD = 'City' THEN JHMOLD ELSE NULL END as OldCity,
CASE WHEN JHMFLD = 'City' THEN JHMNEW ELSE NULL END as NewCity,
CASE WHEN JHMFLD = 'State' THEN JHMOLD ELSE NULL END as OldState,
CASE WHEN JHMFLD = 'State' THEN JHMNEW ELSE NULL END as NewState,
CASE WHEN JHMFLD = 'Zip' THEN JHMOLD ELSE NULL END as OldZip,
CASE WHEN JHMFLD = 'Zip' THEN JHMNEW ELSE NULL END as NewZip
FROM CFMANT
WHERE JHMFLD in ('Address line 1','Address line 2','City','State','Zip')

An Example of the results on multiple rows is:
CIF# MaintDate TimeChanged OldAddress1 NewAddress1 OldCity NewCity
BAG2346 2013-09-09 00:00:00.000 161953 4024 SE 1st Ct 5378 Hawks Landing NULL NULL
BAG2346 2013-09-09 00:00:00.000 161953 NULL NULL Cape Coral Ft Myers

I want to see the data on one row if the CIF#, MaintDate, and TimeChanged are the same.

For the above example, I would like to see the results as:
CIF# MaintDate TimeChanged OldAddress1 NewAddress1 OldCity NewCity
BAG2346 2013-09-09 00:00:00.000 161953 4024 SE 1st Ct 5378 Hawks Landing Cape Coral Ft Myers

Thanks so much for your help!!!
lymi6977

 

[gmmastros]

Try this:

ALTER View [dbo].[vwCIFMaintAddressLines1Or2] as 
SELECT   CAST(JHMCIF as Varchar(15)) as CIF#, 
         dbo.JHAJulianToDate(JHMDTM) as MaintDate, 
         JHMTIM as TimeChanged, 
         Max(CASE WHEN JHMFLD = 'Address line 1' THEN JHMOLD ELSE NULL END) as OldAddress1, 
         Max(CASE WHEN JHMFLD = 'Address line 1' THEN JHMNEW ELSE NULL END) as NewAddress1, 
         Max(CASE WHEN JHMFLD = 'Address line 2' THEN JHMOLD ELSE NULL END) as OldAddress2, 
         Max(CASE WHEN JHMFLD = 'Address line 2' THEN JHMNEW ELSE NULL END) as NewAddress2,
         Max(CASE WHEN JHMFLD = 'City' THEN JHMOLD ELSE NULL END) as OldCity, 
         Max(CASE WHEN JHMFLD = 'City' THEN JHMNEW ELSE NULL END) as NewCity,
         Max(CASE WHEN JHMFLD = 'State' THEN JHMOLD ELSE NULL END) as OldState, 
         Max(CASE WHEN JHMFLD = 'State' THEN JHMNEW ELSE NULL END) as NewState,
         Max(CASE WHEN JHMFLD = 'Zip' THEN JHMOLD ELSE NULL END) as OldZip, 
         Max(CASE WHEN JHMFLD = 'Zip' THEN JHMNEW ELSE NULL END) as NewZip
FROM     CFMANT 
WHERE    JHMFLD in ('Address line 1','Address line 2','City','State','Zip')
GROUP BY CAST(JHMCIF as Varchar(15)), 
         dbo.JHAJulianToDate(JHMDTM)

If this works for you, and you would like me to explain it, please let me know.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom