SQL to list data in tabular structure

Question

MSSQL Table1 has following data:

AttributeID | ProductID | Attribute  | Value      |
--------------------------------------------------
1           | 1111      | Attribute1 | Prod1_Val1 |
2           | 1111      | Attribute2 | Prod1_Val2 |
3           | 1111      | Attribute3 | Prod1_Val3 |
4           | 2222      | Attribute1 | Prod2_Val1 |
5           | 2222      | Attribute2 | Prod2_Val2 |
6           | 2222      | Attribute3 | Prod2_Val3 |
7           | 3333      | Attribute1 | Prod3_Val1 |
8           | 3333      | Attribute2 | Prod3_Val2 |
9           | 3333      | Attribute3 | Prod3_Val3 |
10          | 4444      | Attribute1 | Prod4_Val1 |
11          | 4444      | Attribute2 | Prod4_Val2 |
12          | 5555      | Attribute4 | Prod5_Val1 |

MSSQL Table2 has following Data:

ProductID | ProductName |
--------------------------------------------------
1111      | Product1    |
2222      | Product2    |
3333      | Product3    |
4444      | Product4    |
5555      | Product5    |

The result I would require is:

Product    | Product1   | Product3   | Product3   | Product4   | Product5   |
Attribute1 | Prod1_Val1 | Prod2_Val1 | Prod3_Val1 | Prod4_Val1 | --         |
Attribute2 | Prod1_Val2 | Prod2_Val2 | Prod3_Val2 | Prod4_Val2 | --         |
Attribute3 | Prod1_Val3 | Prod2_Val3 | Prod3_Val3 | --         | --         |
Attribute4 | --         | --         | --         | --         | Prod5_Val1 |
Attribute5 | --         | --         | --         | --         | --         |

I would like to know whether this desired result can be achieved with SQL itself using Table1 and Table2. If yes, please assist me by providing the SQL. Thanks for your timely assistance in advance.

Answer 1

As alluded to by TomTom, you will need to use the SQL Server's PIVOT operator:

SELECT Attribute, [Product1], [Product2], [Product3], [Product4], [Product5]
FROM (
    SELECT ProductName, Attribute, Value
    FROM Table1 
    INNER JOIN Table2 ON ( Table2.ProductId = Table1.ProductId )
) AS SourceTable
PIVOT (
    Min(Value)
    FOR ProductName IN ([Product1], [Product2], [Product3], [Product4], [Product5])
) AS PivotTable

Be aware that you will need to decide up front how many columns (that is, [Product1], [Product2], ..., [ProductN]) you would like to output when writing your SQL query.

Also, since PIVOT aggregates data, the example above uses the Min() function. (You could just as easily use Max(), or a different aggregation function, based upon your needs.) Your output will be NULL for all elements that do not have values.