Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Rotate (Pivot?) Data

Posted on 2011-02-25
8
Medium Priority
?
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 41

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

704 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