Solved

Rotate (Pivot?) Data

Posted on 2011-02-25
8
374 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
Comment Utility
What is your SQL Server version?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:tabish
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Accepted Solution

by:
tabish earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

6 Experts available now in Live!

Get 1:1 Help Now