Union Columns Vertically in SQL

Question

Assume these Tables:

Group: (Id, Title): {1,G1}, {2,G2}, {3,G3}, {4, G4}

Category: (Id, Title): {1, Cat1}, {2, Cat2}, {3, Cat3}, {4, Cat4}

Product: (Id, GroupId, CategoryId, Name):

{1, 1, 1, G1C1P1},
{2, 1, 2, G1C2P2},
{3, 1, 2, G1C2P3},
{4, 2, 2, G2C2P4},
{5, 2, 2, G2C2P5},
{6, 3, 1, G3C1P6},
{7, 3, 3, G3C3P7}

Dealer: (Id, Name): {1, 'Dealer1'}, {2, 'Dealer2'}, {3, 'Dealer3'}

ProductDealer (Id, ProductId (UK), DealerId, LastSale, Number):

{1, 1, 1, 5, '2012-12-10 12:34:31'}, 
{2, 2, 2, 120, '2012-11-10 12:34:31'}, 
{3, 5, 1, 75, '2012-12-02 12:34:31'}

So I going to create a full view of product, this is my first try:

SELECT 
    [PR].[Id],
    [PR].[Name],
    [PR].[GroupId],
    [GR].[Title] AS [Group],
    [PR].[CategoryId],
    [CA].[Title] AS [Category]

FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]

Then I think to add ProductDealer columns to view, so I try this one:

SELECT 
    [PR].[Id],
    [PR].[Name],
    [PR].[GroupId],
    [GR].[Title] AS [Group],
    [PR].[CategoryId],
    [CA].[Title] AS [Category],
    ISNULL(
    (SELECT [PD].[Id] FROM [dbo].[ProductDealer] AS [PD] 
     WHERE [PD].[ProductId] = [PR].[Id]),
     CAST(-1 AS BIGINT)
    ) AS [ProductDealerId],
    ISNULL(
    (SELECT [DE].[Id] FROM [dbo].[Dealer] AS [DE]
    INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
     WHERE [PD].[ProductId] = [PR].[Id]),
     CAST(-1 AS BIGINT)
    ) AS [DealerId],
     ISNULL(
    (SELECT [DE].[Name] FROM [dbo].[Dealer] AS [DE]
    INNER JOIN [dbo].[ProductDealer] AS [PD] ON [DE].[Id] = [PD].[DealerId]
     WHERE [PD].[ProductId] = [PR].[Id]),
     CAST('HaveNotDealer' AS NVARCHAR)
    ) AS [Dealer],
    ISNULL(
    (SELECT [PD].[LastSale] FROM [dbo].[ProductDealer] AS [PD] 
     WHERE [PD].[ProductId] = [PR].[Id]),
    CAST('0001-01-01 00:00:01' AS DATETIME2)
    ) AS [LastSale],
    ISNULL(
    (SELECT [PD].[Number] FROM [dbo].[ProductDealer] AS [PD] 
     WHERE [PD].[ProductId] = [PR].[Id]),
    CAST(0 AS BIGINT)
    ) AS [SaleNumber]

FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]

As you see for each column in ProductDealer I write a full select query I don't know is there any better way to do this? something like union this columns to first view, what is your suggestion to best implement it?

Update

In ProductDealer Table, the ProductId is UK, So each Product could have one dealer or nothing, I want if the product have a dealer get ProductDealer Columns in view and if not get my default values like: (-1, 'HaveNotDealer').

Answer 1

SELECT 
    [PR].[Id],
    [PR].[Name],
    [PR].[GroupId],
    [GR].[Title] AS [Group],
    [PR].[CategoryId],
    [CA].[Title] AS [Category],
    ISNULL([PD].[Id],
    CAST(-1 AS BIGINT)) AS [ProductDealerId],
    ISNULL([D].Id,
    CAST(-1 AS BIGINT)) as DealerId,
    ISNULL([D].Name,
    CAST('HaveNotDealer' AS NVARCHAR)) as DealerName,
    ISNULL(PD.LastSale,
    CAST('0001-01-01 00:00:01' AS DATETIME2)) as LastSale,
    ISNULL([PD].Number,
    CAST(0 AS BIGINT)) as SaleNumber

FROM [dbo].[Product] AS [PR]
INNER JOIN [dbo].[Group] AS [GR] ON [PR].[GroupId] = [GR].[Id]
INNER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
LEFT OUTER JOIN [dbo].[ProductDealer] AS [PD] ON [PR].[Id] = [PD].[ProductId]
LEFT OUTER JOIN [dbo].[Dealer] AS [D] ON [PD].DealerId = [D].Id

Answer 2

Perhaps only answering this question in "spirit", but this is the "Union Columns Vertically" answer I was looking for when I stumbled upon this Question =)

with aa(col1, col2) as (
    select * from (values (1,2) ) blah
),
 bb(col3, col4) as (
    select * from (values (5,6) ) blah
)

select select aa.col1, aa.col2, bb.col3, bb.col4
from aa , bb

Gives

col1|col2|col3|col4
1   |2   |5   |6   

Disclaimer The above only works cleanly for single row aa , bb .