title: How to return all record ids which are also part of the grouping in T-SQL
body: <p>I have a dataset that is laid out in a vertical format with a varying number of attributes per AppId. I currently group by the Attribute and Value to determine the maximum AppId. The scale attribute is being ignored in the Group By using "Where Attribute not in ('Scale')". I would also like to know which other AppId values are included in the grouping.</p>
  
  <p>Below is an example of how the data is structured in the source table followed by how the data would look if it were flattened out. Next, is a query that'll give me the maximum AppId for each unique grouping and what the output looks like. How can I also return the other AppId values which are part of the grouping.</p>
  
  <p>Source table:</p>
  
  <pre><code>+--------+-------+-----------+------------+
  | PartId | AppId | Attribute |   Value    |
  +--------+-------+-----------+------------+
  | ABC    | ABC_1 | Color     | Red        |
  | ABC    | ABC_1 | Shape     | Square     |
  | ABC    | ABC_1 | Material  | Plastic    |
  | ABC    | ABC_1 | Scale     | Celsius    |
  | ABC    | ABC_2 | Color     | RED        |
  | ABC    | ABC_2 | Shape     | Square     |
  | ABC    | ABC_2 | Material  | Plastic    |
  | ABC    | ABC_2 | Scale     | Fahrenheit |
  | ABC    | ABC_3 | Color     | Red        |
  | ABC    | ABC_4 | Color     | Red        |
  | ABC    | ABC_5 | Color     | Green      |
  | ABC    | ABC_5 | Shape     | Round      |
  +--------+-------+-----------+------------+
  </code></pre>
  
  <p>Pivoted representation:</p>
  
  <pre><code>+--------+-------+-------+--------+----------+------------+
  | PartId | AppId | Color | Shape  | Material |   Scale    |
  +--------+-------+-------+--------+----------+------------+
  | ABC    | ABC_1 | Red   | Square | Plastic  | Celsius    |
  | ABC    | ABC_2 | Red   | Square | Plastic  | Fahrenheit |
  | ABC    | ABC_3 | Red   |        |          |            |
  | ABC    | ABC_4 | Red   |        |          |            |
  | ABC    | ABC_5 | Green | Round  |          |            |
  +--------+-------+-------+--------+----------+------------+
  </code></pre>
  
  <p>My query to get the maximum AppId</p>
  
  <pre><code>Select Distinct Max(AppId) As GroupAppId
  From VerticleData
  Where Attribute not in ('Scale')
  Group By Attribute, Value
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>+------------+
  | GroupAppId |
  +------------+
  | ABC_2      |
  | ABC_4      |
  | ABC_5      |
  +------------+
  </code></pre>
  
  <p>Desired Output:</p>
  
  <pre><code>+------------+-------+
  | GroupAppId | AppId |
  +------------+-------+
  | ABC_2      | ABC_1 |
  | ABC_2      | ABC_2 |
  | ABC_4      | ABC_3 |
  | ABC_4      | ABC_4 |
  | ABC_5      | ABC_5 |
  +------------+-------+  
  </code></pre>