CODEWITHSUNDEEP
sql
Mehdi
Mehdi

Reputation: 598

SQL query to show grouped rows in columns

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?

Upvotes: 0

Views: 21

Answers (1)

ahmed
ahmed

Reputation: 9191

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.

Upvotes: 2

Related Questions