Table one
==========
CREATE TABLE [mst].[Car](
[CarId] [int] IDENTITY(1,1) NOT NULL,
[BrandId] [int] NULL,
[ModelId] [int] NULL,
[IsAction] [bit] NOT NULL,
[VarientId] [int] NULL,
[DealerId] [int] NULL,
[OrgInfoId] [int] NULL,
CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED
(
[CarId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
table 2
============
CREATE TABLE [mst].[Dealer](
[DealerId] [int] IDENTITY(1,1) NOT NULL,
[DealerName] [nvarchar](250) NULL,
[Location] [nvarchar](50) NULL,
[SpocPerson] [nvarchar](250) NULL,
[ContactNo] [nvarchar](50) NULL,
[IsAction] [bit] NOT NULL,
CONSTRAINT [PK_Dealer] PRIMARY KEY CLUSTERED
(
[DealerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
table 3
===============
CREATE TABLE [mst].[Model](
[ModelId] [int] IDENTITY(1,1) NOT NULL,
[ModelName] [nvarchar](250) NULL,
[BrandId] [int] NOT NULL,
[IsAction] [bit] NOT NULL,
CONSTRAINT [PK_Model] PRIMARY KEY CLUSTERED
(
[ModelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
out put can this format
==================
Model Total On Loan Available
Passat 1 1 0
Jetta 1 1 0
Sharan 1 1 0
Scirocco 1 0 1
available colum can total - onloan