CODEWITHSUNDEEP
sqlsql-server
Ramirez77
Ramirez77

Reputation: 107

Including columns from duplicate rows in select

I am working on creating a query which would return one row for each primary key. The question is that in the database there is another table I am trying to join with, and in this other table the primary key from the first table can appear multiple times but with a code which describes what type of information is stored in a column called text_info which stores text related to what the code represents.

For example:

PrimaryKey|Code      |text_info
--------------------------------
5555      |1         |1/4/2017
5555      |2         |Approved

What I would am trying to get to is a select statement that would return something like this.

PrimaryKey|Date      |Status
----------------------------------
5555      |1/4/2017  |Approved

I have been trying to join the two tables in various ways but my attempts have always returned multiple rows which I do not want for this query. Any help would be greatly appreciated for this.

Upvotes: 0

Views: 25

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

I think a simple conditional aggregation would do the trick here. If you have a large and/or variable number of codes, you may have to go DYNAMIC.

Select PrimaryKey
      ,Date   = max(case when code=1 then text_info else null end)
      ,Status = max(case when code=2 then text_info else null end)
 From  YourTable
 Group By PrimaryKey

Upvotes: 3

Related Questions