How do I add a column that marks one of each value?

Question

I'm joining two tables in SQL. I currently have the SQL as:

SELECT table1.ProjectName AS "Project Name",
  table1.ProjectCost AS "Project Cost",
  table2.ExpenseName AS "Expense Name",
  table2.ExpenseCost AS "Expense Cost"
  FROM TABLE1 table1
  INNER JOIN TABLE2 table2
  ON table1.ProjectName = table2.ProjectName;

The result looks like:

Project Name    | Project Cost  | Expense Name  | Expense Cost
------------------------------------------------------------
Project 1   | 123456        | Labor     | 12365  
Project 1   | 123456        | Rent      | 120000  
Project 2   | 8421              | (null)    | (null)  
Project 3   | 987654        | Paper     | 1023  
Project 3   | 987654        | Pens      | 546

I want to add a row that marks one of each Project Name so that I can filter over it in Tableau and sum the projects costs.

EX:

Project Name    | Project Cost  | Expense Name  | Expense Cost  | Unique Value
----------------------------------------------------------------------------
Project 1   | 123456        | Labor     | 12365     | Y  
Project 1   | 123456        | Rent      | 12000     | N  
Project 2   | 8421      | (null)    | (null)        | Y  
Project 3   | 987654        | Paper     | 1023      | Y  
Project 3   | 987654        | Pens      | 546       | N  
Project 3   | 987654        | Party     | 9856      | N

Which column is `Project Number`? – PM 77-1 Oct 24 '19 at 15:40 — PM 77-1, Oct 24 '19 at 15:40
Sorry I meant project Name – Phillip Hetzler Oct 24 '19 at 15:41 — Phillip Hetzler, Oct 24 '19 at 15:41
Are you familiar with `EXISTS` clause? – PM 77-1 Oct 24 '19 at 15:42 — PM 77-1, Oct 24 '19 at 15:42

score 1 · Accepted Answer · answered Oct 24 '19 at 15:42

1

I suposse you can use the lag function, I actually asked this not a long time ago, I can share my question, maybe it helps you. But instead of selecting the value you could create a table temporal table and populate that column based on the lag function:

SELECT Only one value per ID - SQL Server

answered Oct 24 '19 at 15:42

FcoLG

52
1
9

I realized I forgot to put my code in so I've added it. How do I use this with my code? – Phillip Hetzler Oct 24 '19 at 17:10

score 1 · Answer 2 · edited Oct 24 '19 at 16:29

1

You can use rank as well:

select t.* 
     , case when lag(project_name) over (partition by 1 order by project_name, rownum) = project_name then 'N' else 'Y' end n
     , case when rank() over (partition by project_name order by rownum) >  1 then 'N' else 'Y' end n
from join_result

edited Oct 24 '19 at 16:29

dx_over_dt

13,240
17
54
102

answered Oct 24 '19 at 15:54

Thomas Strub

1,275
7
20

I realized I forgot to put my code in so I've added it. How do I use this with my code? – Phillip Hetzler Oct 24 '19 at 17:10

How do I add a column that marks one of each value?

2 Answers2