How to combine multiple rows based on column value(s)

Question

I'm just starting out in SQL so as always, apologizes for the notive question. I'm using SQL it for reporting purposes. I'm not trying to write data. I did not set up/nor can I modify any tables in my system.

The problem I can not seem to overcome this past weekend while googling and testing things is I have a functioning query that gives me 1 row of data across various tables per GUID [Main_ID]. I need to pull some data from another table that shares the same [Main ID] and has various but constant secondary IDs [Attribute_ID].

To go into more detail about the table, Attribute_IDs ('Att_1', 'Att_2',...) are constant throughout the system regardless of the Main_ID. And each Attribute_ID always correlates to the same column ('Att_1' = archar255, 'Att_2' = archarMAX). There is only 1 column of data per Attribute ID. The values in the 3 rightmost columns are strings that vary.

Table - Value_Table

+-----------+--------------+----------------+----------------+-------------+
| Main_ID   | Attribute_ID | archar255      | archarMAX      | LookupValue |
| GUID_ID_1 | Att_1        |                | blah blah      |             |
| GUID_ID_1 | Att_2        | 255 value test |                |             |
| GUID_ID_1 | Att_3        |                |                | LV          |
| GUID_ID_1 | Att_4        |                | MAX            |             |
| GUID_ID_1 | Att_5        |                | something else |             |
+-----------+--------------+----------------+----------------+-------------+

Expected Output -

+-----------+-----------+----------------+-------+-------+
| Main_ID   | Att_1     | Att_2          | Att_3 | Att_4 |
| GUID_ID_1 | blah blah | 255 value test | LV    | MAX   |
+-----------+-----------+----------------+-------+-------+

If anyone is willing to help me out, or just simply point me in the right direction, I would be most appreciative.

I have tried using WHERE statements and CASE but it can only point to 1 of the 5 rows that actually exist.

What is logic to get your expected output? Get `archarMAX` for `Att_1` or if `archar255` has value of `Att_1` then get it, if not then check on `archarMAX`, if not then check on `LookupValue`? You say 'Att_1' = archar255, 'Att_2' = archarMAX but in your expected output it is opposite?? — Pham X. Bach, Jan 15 '19 at 01:50

GMB · Accepted Answer · 2019-01-15T01:09:18.377

I guess that you need conditional aggregation :

SELECT
    vt.Main_ID,
    MAX( CASE WHEN vt.attribute_ID = 'Att_1' THEN vt.archarMAX END) Att1,
    MAX( CASE WHEN vt.attribute_ID = 'Att_2' THEN vt.archar255 END) Att2,
    MAX( CASE WHEN vt.attribute_ID = 'Att_3' THEN vt.LookupValue END) Att3,
    MAX( CASE WHEN vt.attribute_ID = 'Att_4' THEN vt.archarMAX END) Att4
FROM Value_Table vt
GROUP BY vt.Main_ID;

Here is a db fiddle with your sample data (since you did not tag your RDBMS, I chose MySQL, but this should work almost everywhere) :

 WITH Value_Table AS (
     SELECT 'GUID_ID_1' Main_ID, 'Att_1' Attribute_ID, null archar255, 'blah blah' archarMAX, null LookupValue
     UNION SELECT 'GUID_ID_1', 'Att_2', '255 value test', null, null
     UNION SELECT 'GUID_ID_1', 'Att_3', null, null, 'LV'
     UNION SELECT 'GUID_ID_1', 'Att_4', null, 'MAX', null
     UNION SELECT 'GUID_ID_1', 'Att_5', null, 'something else', null
 )
 SELECT
     vt.Main_ID,
     MAX( CASE WHEN vt.attribute_ID = 'Att_1' THEN vt.archarMAX END) Att1,
     MAX( CASE WHEN vt.attribute_ID = 'Att_2' THEN vt.archar255 END) Att2,
     MAX( CASE WHEN vt.attribute_ID = 'Att_3' THEN vt.LookupValue END) Att3,
     MAX( CASE WHEN vt.attribute_ID = 'Att_4' THEN vt.archarMAX END) Att4
 FROM Value_Table vt
 GROUP BY vt.Main_ID


 Main_ID   | Att1      | Att2           | Att3 | Att4
 :-------- | :-------- | :------------- | :--- | :---
 GUID_ID_1 | blah blah | 255 value test | LV   | MAX

Thank you very much! This is why I love this community so much — Scott, Jan 15 '19 at 19:12

Gauravsa · Answer 2 · 2019-01-15T04:52:03.290

Here is with pivot:

    select main_id, max([Att_1]) as Att_1, max([Att_2]) as Att_2, max([Att_3]) as Att_3, max([Att_4]) as Att_4
from 
(
  select main_id, Attribute_ID, 
   Attribute_ID+'1' As Attribute_ID1,
   Attribute_ID+'2' As Attribute_ID2,
   archar255, archarMax, Lookupvalue
  from #yt
) as src
pivot
(
  max(archarMax)
  for Attribute_ID in ([Att_1], [Att_2], [Att_3], [Att_4])
) as piv

group by main_id

How to combine multiple rows based on column value(s)

2 Answers2