Rotate (Pivot?) Data

I have the following query:
SELECT
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code, 
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account, 
	SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount) AS Debit_Amt, 
	SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount) AS Credit_Amt,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3
FROM
	dbo.VN_GL_DISTRIBUTION_HEADER_MC 
	INNER JOIN dbo.VN_GL_DISTRIBUTION_DETAIL_MC 
		ON dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Company_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Type_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Type_Code
WHERE
	(
		dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '1200' 
		OR dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '2300' 
	) 
group by 
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3
order by 
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3

Open in new window


This will give me the following results:
Vendor_Code  GL_Dist_Acct  Debit_Amt  Credit_Amt  Post_Date
VENDOR1      1200          2869.73    0.00        2011-01-31
VENDOR1      2300          1800.27    0.00        2011-02-14
VENDOR2      1200           391.71    0.00        2011-02-22
VENDOR3      1200           930.84    0.00        2011-02-24
VENDOR3      2300          2763.54    0.00        2010-12-31

Open in new window


What I need is a columns (Debit. Credit, and Date) for each of the two GL_Dist_Acct.  
Something like this:
Vendor_Code  1200_Debit  1200_Credit 1200_Post_Date  2300_Debit_Amt  2300_Credit_Amt  2300_Post_Date
VENDOR1      2869.73         0.00    2011-01-31      1800.27         0.00             2011-02-14
VENDOR2       391.71         0.00    2011-02-22         0.00         0.00             
VENDOR3       930.84         0.00    2011-02-24      2763.54         0.00             2010-12-31

Open in new window

LVL 10
ClifAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
tabishConnect With a Mentor Commented:
Ops! after a little tweak this one should give the desired results: If you are using sql2005 or above you may want to consider using CTE. that will be a little cleaner.
Select a.Vendor_Code,Max(1200_Debit_Amt), Max(1200_Credit_Amt), Max(1200_Date_List3),Max(2300_Debit_Amt), Max(2300_Credit_Amt), Max(2300_Date_List3)

from 

(SELECT
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code, 
	Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount) else 0 End AS 1200_Debit_Amt,

Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount) else 0 End AS 1200_Credit_Amt,
 
Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3 else Null End AS 1200_Date_List3,
dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code, 
	Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount) else 0 End AS 2300_Debit_Amt,

Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount) else 0 End AS 2300_Credit_Amt,
 
Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3 else Null End AS 2300_Date_List3

FROM
	dbo.VN_GL_DISTRIBUTION_HEADER_MC 
	INNER JOIN dbo.VN_GL_DISTRIBUTION_DETAIL_MC 
		ON dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Company_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Type_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Type_Code
WHERE
	(
		dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '1200' 
		OR dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '2300' 
	) 
group by 
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3

)) As a

Group by
a.Vendor_Code,
1200_Debit_Amt, 
1200_Credit_Amt, 
1200_Date_List3,
2300_Debit_Amt, 
2300_Credit_Amt, 
2300_Date_List3

Open in new window

0
 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
Aaron ShiloChief Database ArchitectCommented:
read this about using PIVOT/UNPIVOT
from sql server 2005

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ClifAuthor Commented:
What is your SQL Server version?
It's either 2005 or newer.

read this about using PIVOT/UNPIVOT
from sql server 2005

I did.  I'm not sure I understand it.  Can you take my query and apply it to the PIVOT clause?
0
 
tabishCommented:
I think what you want may not be possible with pivot table. I have not tried it though. but case statement should do the trick. The Only disadvantage with it is the GL_Dist_Acct will be hard coded.

I also assumed GL_Dist_Acct  is a numeric field. if not you will have o change the code.
please try the attached code:



SELECT
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code, 
	Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount) else 0 End AS 1200_Debit_Amt,

Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount) else 0 End AS 1200_Credit_Amt,
 
Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 1200 then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3 else Null End AS 1200_Date_List3,
dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code, 
	Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount) else 0 End AS 2300_Debit_Amt,

Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then SUM(dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount) else 0 End AS 2300_Credit_Amt,
 
Case When dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = 2300 then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3 else Null End AS 2300_Date_List3

FROM
	dbo.VN_GL_DISTRIBUTION_HEADER_MC 
	INNER JOIN dbo.VN_GL_DISTRIBUTION_DETAIL_MC 
		ON dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Company_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number 
		AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Type_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Type_Code
WHERE
	(
		dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '1200' 
		OR dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '2300' 
	) 
group by 
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3
order by 
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
	dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account,
	dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3

Open in new window

0
 
ispalenyCommented:
SELECT
      dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code,
      SUM(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='1200'
            then dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount
            else 0
            end) as Debit_Amt_1200,
      SUM(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='1200'
            then dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount
            else 0
            end) as Credit_Amt_1200,
      MAX(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='1200'
            then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3
            else NULL
            end) as Post_Date_1200,
      SUM(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='2300'
            then dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Debit_Amount
            else 0
            end) as Debit_Amt_2300,
      SUM(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='2300'
            then dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Credit_Amount
            else 0
            end) as Credit_Amt_2300,
      MAX(case
            when dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account='2300'
            then dbo.VN_GL_DISTRIBUTION_HEADER_MC.Date_List3
            else NULL
            end) as Post_Date_2300

FROM
      dbo.VN_GL_DISTRIBUTION_HEADER_MC
      INNER JOIN dbo.VN_GL_DISTRIBUTION_DETAIL_MC
            ON dbo.VN_GL_DISTRIBUTION_HEADER_MC.Company_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Company_Code
            AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Vendor_Code
            AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Number = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Number
            AND dbo.VN_GL_DISTRIBUTION_HEADER_MC.Invoice_Type_Code = dbo.VN_GL_DISTRIBUTION_DETAIL_MC.Invoice_Type_Code
WHERE
      (
            dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '1200'
            OR dbo.VN_GL_DISTRIBUTION_DETAIL_MC.GL_Distribution_Account = '2300'
      )
group by
      dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code
order by
      dbo.VN_GL_DISTRIBUTION_HEADER_MC.Vendor_Code
0
 
ClifAuthor Commented:
tabish,
Not sure what the difference is, other than the order in which the data is displayed.  But it seems to work.

Once the data checking team takes a look at the final report, I'll know for sure.

Thanks
0
 
ClifAuthor Commented:
ispaleny:

I don't think yours is quite right.  There appears to be only one Vendor per line.  This is not quiite right.  Some vendors may have nmore than one credit or debit on different days.  These nood to show up as separate rows.

Thanks anyway.
0
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.

All Courses

From novice to tech pro — start learning today.