Formatted output

  • I have a table with fields FeeId and Amount as below. FeeID is a dynamic value..

    FeeID Amount

    1 100

    2 150

    3 200

    4 250

    5 300

    I have another table with fields EmpId and FeeID as below

    EmpIDFeeID

    1 1

    1 2

    1 3

    2 1

    2 4

    3 3

    3 5

    Required output

    EmpID FeeID Amount

    1 1 100

    1 2 150

    1 3 200

    1 4 0

    1 5 0

    2 1 100

    2 2 0

    2 3 0

    2 4 250

    2 5 0

    3 1 0

    3 2 0

    3 3 200

    3 4 0

    3 5 300

  • Please post data in a consumable way.

    Show us what you have tried.

    See here for posting guidelines: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    You're just a few clicks away from a good question!

    -- Gianluca Sartori

  • To provide an actual, tested query, we'll also need to know the table names. This is pretty simple, so I'd also like to see what you've tried so far.

  • IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[allowances]') AND type in (N'U'))

    DROP TABLE [dbo].[allowances]

    GO

    CREATE TABLE [dbo].[allowances](

    [allid] [int] NULL,

    [name] [nchar](10) NULL,

    [amount] [int] NULL

    ) ON [PRIMARY]

    GO

    insert into allowances values(1,'a',10)

    insert into allowances values(2,'b',20)

    insert into allowances values(3,'c',25)

    insert into allowances values(4,'d',30)

    insert into allowances values(5,'e',35)

    go

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[allowance]') AND type in (N'U'))

    DROP TABLE [dbo].[allowance]

    GO

    CREATE TABLE [dbo].[allowance](

    [empid] [int] NULL,

    [allowanceid] [int] NULL

    ) ON [PRIMARY]

    GO

    insert into allowance values(1,1)

    insert into allowance values(1,2)

    insert into allowance values(1,3)

    insert into allowance values(2,1)

    insert into allowance values(2,4)

    insert into allowance values(3,3)

    insert into allowance values(3,5)

    go

    --I tried

    select distinct allowance.empid,allowances.allid,allowances.amount

    from allowances left outer join allowance on allowances.allid=allowance.allowanceid

    order by empid

    --Needed as below

    EmpID FeeID Amount

    1 1 10

    1 2 20

    1 3 25

    1 4 0

    1 5 0

    2 1 10

    2 2 0

    2 3 0

    2 4 30

    2 5 0

    3 1 0

    3 2 0

    3 3 25

    3 4 0

    3 5 35

  • You need to make a Cartesian of the 2 tables and then left join them, like this

    WITH a AS (

    SELECT DISTINCT allowance.empid,allowances.allid

    FROM allowance

    CROSS JOIN allowances

    )

    SELECT a.empid,a.allid,ISNULL(allowances.amount,0) AS [amount]

    FROM a

    LEFT JOIN allowance ON allowance.empid = a.empid

    AND allowance.allowanceid = allid

    LEFT JOIN allowances on allowances.allid=allowance.allowanceid

    ORDER BY empid ASC,allid ASC;

    Far away is close at hand in the images of elsewhere.
    Anon.

  • thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply