Change rows to columns in SQL

Question

I have a query like this:

SELECT ItemMaster.ERPItemCode, DistributorStock.Qty
FROM DistributorStock INNER JOIN
ItemMaster ON DistributorStock.ItemMasterId = ItemMaster.Id
WHERE (DistributorStock.DistributionCenterId = 2)

Results are attached:

enter image description here

I want to change the query to show Item Code in columns, not in rows. How can I achieve this?

score 5 · Accepted Answer · edited May 23 '17 at 11:51

Use PIVOT operator. If your list of codes is dynamic and you don't know the full list in time of writing the query, you have to use dynamic pivot, check this question.

Sample:

select 'TotalQty', [DHS20], [DHP12], [DHP10], [DHL12], ...
from
(SELECT ItemMaster.ERPItemCode, DistributorStock.Qty
    FROM DistributorStock
    JOIN ItemMaster ON DistributorStock.ItemMasterId = ItemMaster.Id
    WHERE (DistributorStock.DistributionCenterId = 2)
) src
pivot
(sum(src.Qty)
for src.ERPItemCode in ([DHS20], [DHP12], [DHP10], [DHL12], ...)
) as pvt

You can replace sum aggregate function with any other as you wish. For dynamic query, you have to build the query at runtime, building the lists of columns from some other query. You can find how to concatenate strings in this question.

Change rows to columns in SQL

1 Answers1