Union Columns Vertically in SQL

sql, sql-server, sql-server-2008, t-sql

Solution

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

Problem

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

Original source