USE [...]
GO
/****** Object:  Table [dbo].[property_char]    Script Date: 11/19/2009 10:56:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[property_char](
	[prop_char_typ_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[tax_year] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[property_id] [int] NOT NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
	[value] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[prop_segment_id] [int] NULL CONSTRAINT [df__property___prop___0b91ba14]  DEFAULT (0),
 CONSTRAINT [pkx_property_char1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Statistic [s_idx_property_char2]    Script Date: 11/19/2009 10:56:53 ******/
CREATE STATISTICS [s_idx_property_char2] ON [dbo].[property_char]([value])
GO
/****** Object:  Statistic [s_idx_property_char3]    Script Date: 11/19/2009 10:56:53 ******/
CREATE STATISTICS [s_idx_property_char3] ON [dbo].[property_char]([property_id], [tax_year], [prop_char_typ_code], [prop_segment_id])
GO
/****** Object:  Statistic [s_idx_property_char4]    Script Date: 11/19/2009 10:56:53 ******/
CREATE STATISTICS [s_idx_property_char4] ON [dbo].[property_char]([tax_year], [prop_char_typ_code])
GO
/****** Object:  Statistic [s_idx_property_char5]    Script Date: 11/19/2009 10:56:53 ******/
CREATE STATISTICS [s_idx_property_char5] ON [dbo].[property_char]([prop_segment_id], [prop_char_typ_code], [tax_year])
GO
/****** Object:  Statistic [s_pkx_property_char1]    Script Date: 11/19/2009 10:56:53 ******/
CREATE STATISTICS [s_pkx_property_char1] ON [dbo].[property_char]([id])

select
    p.id,
    rtrim( p.parcel_number ) as parcel_id,
    rtrim( cpr.value ) + 'XX' as cpr,
    rtrim( cprx.value ) as cprx,
    rtrim( nbhd.value ) as nbhd,
    rtrim( pclas.value ) as pclas,
    rtrim( dcls.value ) as decls,
    rtrim( zone.value ) as zone,
    rtrim( defd.value ) as defd,
    rtrim( acres.value ) as acres,
    rtrim( fp.value ) as fp_acres,
    rtrim( tbr.value ) as tbr_acres,
    rtrim( grz.value ) as grz_acres,
    rtrim( ivpp.value ) as ivpp,
    rtrim( aa.value ) as aa,
    rtrim( cuse.value ) as cuse,
    rtrim( yr.value ) as yr,
    rtrim( farm.value ) as farm,
    rtrim( mblhm.value ) as mblhm,
    rtrim( manf.value ) as manf,
    rtrim( ppcat.value ) as ppcat,
    rtrim( ppcls.value ) as ppcls,
    rtrim( vetyr.value ) as vetyr,
    rtrim( sc.value ) as sc,
    rtrim( sutil.value ) as sutil
from
    AscendWheelerMain..property p left outer join
	AscendWheelerMain..property_char nbhd on
    p.id = nbhd.property_id
        and nbhd.tax_year = 2008--@tax_year
        and nbhd.prop_char_typ_code = 'NBHD' left outer join
    AscendWheelerMain..property_char cpr on
    p.id = cpr.property_id
        and ( cpr.tax_year = nbhd.tax_year or cpr.tax_year is null )
        and cpr.prop_char_typ_code = 'CPR' left outer join
    AscendWheelerMain..property_char cprx on
    p.id = cprx.property_id
        and ( cprx.tax_year = nbhd.tax_year or cprx.tax_year is null )
        and cprx.prop_char_typ_code = 'CPRX' left outer join
    AscendWheelerMain..property_char pclas on
    p.id = pclas.property_id
        and ( pclas.tax_year = nbhd.tax_year or pclas.tax_year is null )
        and pclas.prop_char_typ_code = 'PCLAS' left outer join
    AscendWheelerMain..property_char zone on
    p.id = zone.property_id
        and ( zone.tax_year = nbhd.tax_year or zone.tax_year is null )
        and zone.prop_char_typ_code = 'ZONE' left outer join
    AscendWheelerMain..property_char acres on
    p.id = acres.property_id
        and ( acres.tax_year = nbhd.tax_year or acres.tax_year is null )
        and acres.prop_char_typ_code = 'SIZE' left outer join
    AscendWheelerMain..property_char fp on
    p.id = fp.property_id
        and ( fp.tax_year = nbhd.tax_year or fp.tax_year is null )
        and fp.prop_char_typ_code = 'FSIZE' left outer join
    AscendWheelerMain..property_char tbr on
    p.id = tbr.property_id
        and ( tbr.tax_year = nbhd.tax_year or tbr.tax_year is null )
        and tbr.prop_char_typ_code = 'FASZ' left outer join
    AscendWheelerMain..property_char grz on
    p.id = grz.property_id
        and ( grz.tax_year = nbhd.tax_year or grz.tax_year is null )
        and grz.prop_char_typ_code = 'GASZ' left outer join
    AscendWheelerMain..property_char dcls on
    p.id = dcls.property_id
        and ( dcls.tax_year = nbhd.tax_year or dcls.tax_year is null )
        and dcls.prop_char_typ_code = 'DECLS' left outer join
    AscendWheelerMain..property_char defd on
    p.id = defd.property_id
        and ( defd.tax_year = nbhd.tax_year or defd.tax_year is null )
        and defd.prop_char_typ_code = 'DEFD' left outer join
    AscendWheelerMain..property_char ivpp on
    p.id = ivpp.property_id
        and ( ivpp.tax_year = nbhd.tax_year or ivpp.tax_year is null )
        and ivpp.prop_char_typ_code = 'IVPP' left outer join
    AscendWheelerMain..property_char aa on
    p.id = aa.property_id
        and ( aa.tax_year = nbhd.tax_year or aa.tax_year is null )
        and aa.prop_char_typ_code = 'AA' left outer join
    AscendWheelerMain..property_char cuse on
    p.id = cuse.property_id
        and ( cuse.tax_year = nbhd.tax_year or cuse.tax_year is null )
        and cuse.prop_char_typ_code = 'CUSE' left outer join
    AscendWheelerMain..property_char yr on
    p.id = yr.property_id
        and ( yr.tax_year = nbhd.tax_year or yr.tax_year is null )
        and yr.prop_char_typ_code = 'YR' left outer join
    AscendWheelerMain..property_char farm on
    p.id = farm.property_id
        and ( farm.tax_year = nbhd.tax_year or farm.tax_year is null )
        and farm.prop_char_typ_code = 'FARM' left outer join
    AscendWheelerMain..property_char mblhm on
    p.id = mblhm.property_id
        and ( mblhm.tax_year = nbhd.tax_year or mblhm.tax_year is null )
        and mblhm.prop_char_typ_code = 'MBLHM' left outer join
    AscendWheelerMain..property_char manf on
    p.id = manf.property_id
        and ( manf.tax_year = nbhd.tax_year or manf.tax_year is null )
        and manf.prop_char_typ_code = 'MANF' left outer join
    AscendWheelerMain..property_char ppcat on
    p.id = ppcat.property_id
        and ( ppcat.tax_year = nbhd.tax_year or ppcat.tax_year is null )
        and ppcat.prop_char_typ_code = 'PPCAT' left outer join
    AscendWheelerMain..property_char ppcls on
    p.id = ppcls.property_id
        and ( ppcls.tax_year = nbhd.tax_year or ppcls.tax_year is null )
        and ppcls.prop_char_typ_code = 'PPCLS' left outer join
    AscendWheelerMain..property_char vetyr on
    p.id = vetyr.property_id
        and ( vetyr.tax_year = nbhd.tax_year or vetyr.tax_year is null )
        and vetyr.prop_char_typ_code = 'VETYR' left outer join
    AscendWheelerMain..property_char sc on
    p.id = sc.property_id
        and ( sc.tax_year = nbhd.tax_year or sc.tax_year is null )
        and sc.prop_char_typ_code = 'SC' left outer join
    AscendWheelerMain..property_char sutil on
    p.id = sutil.property_id
        and ( sutil.tax_year = nbhd.tax_year or sutil.tax_year is null )
        and sutil.prop_char_typ_code = 'SUTIL'
where
    p.eff_to_date is null
order by p.id;