SQL query to show grouped rows in columns

user0o2j 0
Viewed 17

Query:

SELECT ProductId,LanguageId,FileType 
FROM dbo.ProductFile
GROUP BY ProductId,FileType,LanguageId

Result:

===============================
| Code | Language | File Type |
-------------------------------
| 2040 | English  | manual    |
| 2040 | English  | exploded  |
| 2040 | English  | catalog   |
| 2040 | Dutch    | catalog   |
| 2041 | English  | manual    |
| 2041 | English  | exploded  |
| 2041 | Dutch    | catalog   |
| 2041 | Dutch    | manual    |
===============================

Target: Show each product in each language in a row with each file type in a column

=================================================
| Code | Language | manual | exploded | catalog |
-------------------------------------------------
| 2040 | English  | true   | true     | true    |
| 2040 | Dutch    | false  | false    | true    |
| 2041 | English  | true   | true     | false   |
| 2041 | Dutch    | true   | false    | true    |
=================================================

Anyone has experienced please?

sql
1 Answers

You may use conditional aggregation as the following:

SELECT Code, Language,
       MAX(CASE FileType WHEN 'manual' THEN 'true' ELSE 'false' END) manual,
       MAX(CASE FileType WHEN 'exploded' THEN 'true' ELSE 'false' END) exploded,
       MAX(CASE FileType WHEN 'catalog' THEN 'true' ELSE 'false' END) catalog
FROM ProductFile
GROUP BY Code, Language
ORDER BY Code, Language

See a demo.

userGpjR0
Related