Solved

Rotate (Pivot?) Data

Posted on 2011-02-25
8
383 Views
Last Modified: 2013-11-05
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

0
Comment
Question by:Clif
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34983930
What is your SQL Server version?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34986170
read this about using PIVOT/UNPIVOT
from sql server 2005

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
LVL 10

Author Comment

by:Clif
ID: 34987219
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 4

Expert Comment

by:tabish
ID: 34988280
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
 
LVL 4

Accepted Solution

by:
tabish earned 500 total points
ID: 34988324
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 34993641
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
 
LVL 10

Author Closing Comment

by:Clif
ID: 34996872
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
 
LVL 10

Author Comment

by:Clif
ID: 34996889
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now