MSSQL Query to change Row records into Column

Hello there,

I have a complex query where I want to show the row result in Column. If I have not mistaken it is called Pivoting. Please see the screenshot for more details.

SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' +  RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS ProductCode,
    dbo.Province.name AS Province,
     SUM(dbo.SaleReturnDetail.quantity) AS SalesReturnQty,
    dbo.Tafsil.description as ProductName,
    (CASE 
     WHEN (dbo.SaleDetail.type = 0 ) THEN 'Sales'
                                            ELSE 'Discount' 
 END) as SaleType,
 cast(Receipt.receiptDate as date) as receiptDate
FROM
    dbo.Sale
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.Sale.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.SaleReturn
ON
    (
        dbo.Sale.id = dbo.SaleReturn.saleId)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.SaleReturnDetail
ON
    (
        dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleReturnDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.SaleReturn.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
        INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-07-23'
AND dbo.Receipt.receiptDate < '2015-08-23'
GROUP BY
    dbo.SaleDetail.type,
    dbo.Tafsil.description,
    dbo.Province.name,
    kol.code,
    Mohin.code,
    Tafsil.code,
    cast(Receipt.receiptDate as date)

Open in new window

e1.gif
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven KribbeSoftware EngineerCommented:
normally I would go like this...

select productcode,sum(case when saletype='Sales' then SalesReturnQty else null end) as sales,
           sum(case when saletype='Discount' then SalesReturnQty else null end) as Discount
from (<your table>) your table
group by productcode

Should do the trick.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, that is called pivoting. Like the above suggestion it requires a fixed, well-known set of criteria, as it has to create a fixed number of columns.
If that is not hte case, you can use XML Path to create a (e.g. comma-)separated llist of values.
In this particular case I would indeed stay with the simple approach shown above.
0
PortletPaulfreelancerCommented:
Like skribbe above I too would suggest use of case expressions inside the SUM() function.  This approach will produce 2 output columns for quantity, one for returns and non-returns. Note I have included a test for OR dbo.SaleDetail.type IS NULL and also note it is now necessary to remove dbo.SaleDetail.type from the group by clause.
SELECT
      RIGHT('000' + CAST(kol.code AS varchar), 3) 
      + RIGHT('00' + CONVERT(varchar, dbo.Mohin.code), 3) 
      + '' 
      + RIGHT('0000' + CAST(tafsil.code AS varchar), 4) AS ProductCode
    , dbo.Tafsil.description AS ProductName
    , dbo.Province.name AS Province
    , CAST(Receipt.receiptDate AS date) AS receiptDate
    , SUM(CASE WHEN dbo.SaleDetail.type = 0 THEN dbo.SaleReturnDetail.quantity ELSE 0 END) AS SalesReturnQty
    , SUM(CASE WHEN dbo.SaleDetail.type <> 0 OR dbo.SaleDetail.type IS NULL THEN dbo.SaleReturnDetail.quantity ELSE 0 END) AS SalesNONReturnQty
FROM dbo.Sale
      INNER JOIN dbo.DeliveryOrder ON dbo.Sale.orderId = dbo.DeliveryOrder.id
      INNER JOIN dbo.Customer ON dbo.DeliveryOrder.customerId = dbo.Customer.id
      INNER JOIN dbo.SaleReturn ON dbo.Sale.id = dbo.SaleReturn.saleId
      INNER JOIN dbo.ContactInformation ON dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id
      INNER JOIN dbo.Address ON dbo.ContactInformation.addressId = dbo.Address.id
      INNER JOIN dbo.City ON dbo.Address.cityId = dbo.City.id
      INNER JOIN dbo.District ON dbo.City.districtId = dbo.District.id
      INNER JOIN dbo.Province ON dbo.District.provinceId = dbo.Province.id
      INNER JOIN dbo.SaleReturnDetail ON dbo.SaleReturn.id = dbo.SaleReturnDetail.saleReturnId
      INNER JOIN dbo.Batch Batch_alias1 ON dbo.SaleReturnDetail.batchId = Batch_alias1.id
      INNER JOIN dbo.Receipt ON dbo.SaleReturn.invoiceReceiptId = dbo.Receipt.id
      INNER JOIN dbo.Tafsil ON Batch_alias1.productId = dbo.Tafsil.id
      INNER JOIN dbo.Mohin ON dbo.Tafsil.mohinId = dbo.Mohin.id
      INNER JOIN dbo.Kol ON dbo.Mohin.kolId = dbo.Kol.id
      INNER JOIN dbo.SaleDetail ON dbo.SaleReturnDetail.saleDetailId = dbo.SaleDetail.id
WHERE dbo.Receipt.receiptDate >= '20150723'
      AND dbo.Receipt.receiptDate < '20150823'
GROUP BY
         kol.code
       , Mohin.code
       , Tafsil.code
       , dbo.Tafsil.description
       , dbo.Province.name
       , CAST(Receipt.receiptDate AS date)
;

Open in new window

Using case expressions inside aggregation functions such as SUM() does "pivot" the data from rows into columns. There is also a PIVOT operator in T-SQL but it is not necessary to use it for this query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Thanks guys for your feedbacks.

Paul:

Thanks so much for your query help!!
0
zolfAuthor Commented:
cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.