Based on your expected output (where the values from the row are the column names) you do need to use PIVOT. However, because of the table schema you will need to use either UNPIVOT or UNION to get the data into the form you need first. That result will need to be either in a sub-query, a temporary table or a common table expression.
Because this is homework I will not give you the full code here, but here are the steps you need to take …
1. Get the data into the format
itemid Attrib AttValue
4 XYZ A
4 Color White
4 PQR aaaaa
I would use
UNPIVOT[
^] to do this. There is a worked example here -
SQL Banana: SQL UNPIVOT on Multiple Columns[
^]. Take careful note of the WHERE clause in the example.
If you don't use UNPIVOT then you will need to select each attribute name one by one and UNION the results.
2. You can put the results into a
Sub-Query[
^], a
temporary table[
^], a
table variable[
^] or a
Common Table Expression (CTE)[
^]. My personal preference is a CTE but a temporary table will do.
3. You then need to PIVOT (See CP article
Simple Way To Use Pivot In SQL Query[
^] ) that data to get the result you want.
Hint: Pivots always needs some sort of cumulative function in the PIVOT clause. In this case I would use MAX().
Give it a go and if you are still having problems, respond with the code you have tried and I will try to guide you further.
Submit an article or tip