I have table in SQL Server called test having 3 column
| ITEM | ATTRIBUTE | VALUE |
-----------------------------
| item1 | Quality | A |
| item1 | color | Red |
| item2 | Quality | B |
| item2 | color | Black |
I want output like this:
| ITEM | QUALITY | COLOR |
---------------------------
| item1 | A | Red |
| item2 | B | Black |
How can I get this in SQL Server.
Then you apply the aggregate function sum() with the case statement to get the new columns for each color . The inner query with the UNPIVOT performs the same function as the UNION ALL . It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.
Here's how you can transpose cell content: Copy the cell range. Select the empty cells where you want to paste the transposed data. On the Home tab, click the Paste icon, and select Paste Transpose.
Display Row Values as Columns in MySQL Dynamically You can customize the above query as per your requirements by adding WHERE clause or JOINS. If you want to transpose only select row values as columns, you can add WHERE clause in your 1st select GROUP_CONCAT statement.
Try this one:
SELECT *
FROM (SELECT Item, attribute, value FROM MyTable) AS t
PIVOT
(
MAX(value)
FOR attribute IN([Quality], [Color])
) AS p;
Output:
╔═══════╦═════════╦═══════╗
║ ITEM ║ QUALITY ║ COLOR ║
╠═══════╬═════════╬═══════╣
║ item1 ║ A ║ Red ║
║ item2 ║ B ║ Black ║
╚═══════╩═════════╩═══════╝
You can also use this dynamic query if you don't know the specific value of attribute
:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute)
from MyTable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Item,' + @cols + '
from
(
Select Item, attribute , value
from MyTable
) dta
pivot
(
MAX(Value)
for attribute in (' + @cols + ')
) pvt '
execute(@query);
This is a bit of a hacky solution for mysql as PIVOT doesn't work in it.
select item, GROUP_CONCAT('',q) as Quantity, GROUP_CONCAT('',c) as Color from
(select item ,CASE WHEN attribute = 'Quality' THEN value ELSE NULL END as q, CASE WHEN attribute = 'Color' THEN value ELSE NULL END as c
from MyTable
) temp
group by item
Issue with this solution is that you should know all the distinct values of attribute column.
You can try it here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With