SQL Server : Merge output based on ID

I have a SQL Server table containing multiple file names and their versions.

When I run

SELECT AssetID, File, Version
FROM tblAssets

I get

|  AssetID  |  File    |  Version |
+-----------+----------+----------+
|  1        |  FileA   |  1.1     |
|  1        |  FileB   |  3.0     |
|  2        |  FileA   |  1.2     |
|  2        |  FileB   |  2.9     |

I’d like the output to be

|  AssetID  |  FileA Version   |  FileB Version | 
+-----------+------------------+----------------+
|  1        |  1.1             |  3.0           |
|  2        |  1.2             |  2.9           |

I believe this can be achieved by using CASE, but haven’t been successful and therefore seek your advise.

Answer

Use conditional aggregation:

select assetId,
       max(case when file = 'FileA' then version end) as filea_version,
       max(case when file = 'FileB' then version end) as fileb_version
from t
group by assetId

Advertisement

Answer