Solved

SQL Query Help partition/pivot data into rows help?

Posted on 2015-01-22
8
86 Views
Last Modified: 2015-01-23
Hello all,

Okay this is what I am trying to accomplish and this may be confusing so hopefully my same data set here will clarify ok.

I have a table with the following data:

DataSourceID = 6 is considered Cust Group 1
DataSourceID = 8 is considered Cust Group 2
DataSourceID = NULL is considered  Cust Override

ID          CustDate,       CustID,     DataSourceID,     Currency,      Amount
15         1/22/2015      1                6                             USD               55.00
14         1/22/2015      1                6                             USD               75.00
13         1/19/2015      1                6                             AED               69.00
12         1/19/2015      1                8                             USD               99.99
11         1/18/2015      1                6                             USD              111.11
10         1/18/2015      1                6                             USD              222.22
9           1/18/2015      1                NULL                      AUD             888.55
8           1/18/2015      1                NULL                      AUD             547.22
7           1/17/2015      1                8                             BBD              593.55
6           1/17/2015      1                6                             USD              444.44
5           1/17/2015      1                NULL                     USD               333.33


What I need to return is 7 columns in my result set that would be as follows I will pass it a CustID = 1:

CustDate    CustGrp1Currency  CustGrp1Amount   CustGrp2Currency CustGrp2Amount  CustOVCurrency   CustOVAmount

I need the data to be grouped by CustDate,  DataSourceID,   Max(ID) to get all three sets of data for each date.   So my resultset would be in this sample set:
   
CustDate    CustGrp1Currency  CustGrp1Amount   CustGrp2Currency CustGrp2Amount  CustOVCurrency   CustOVAmount

1/22/2015   USD                          55.00                         NULL                         NULL                       NULL                       NULL
1/19/2015   AED                          69.00                         USD                           99.99                       NULL                       NULL
1/18/2015   USD                         111.11                        NULL                         NULL                       AUD                        888.55
1/17/2015   USD                         444.44                        BBD                          593.55                     USD                         333.33

Hope this makes sense but I have to get that data together from this one table based on a CustID I pass at the proc.   Thanks all for any help
0
Comment
Question by:sbornstein2
[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
  • 5
  • 2
8 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 40565936
I think your example is incorrect, 1/22/2015 has two payments in USD 55 and 75. you need to sum(amount)

IS the following acceptable
CustDate,custgrp1currency,custgrp1amount,custgrp2currency,custgrp2amount,custgrp3currency,custgrp3amount,
1/22/2015,usd,130,null,null,null,null


What is your attempt at this query?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 40565945
try this....

i  think you basically want to pivot the data for a customer taking only the latest row per day for that customer  and datasourceid

;with cte as
 (select custid,custdate,
     case datasourceid when 6 then currency end as custgrp1currency
    ,case datasourceid when 6 then amount end asas custgrp1amount
    ,case datasourceid when 8 then currency end as custgrp2currency
    ,case datasourceid when 8 then amount end as custgrp2amount
    ,case when datasourceid is null then currency end as custovrcurrency
    ,case when datasourceod is null then amount end as custovramount
    ,row_number() over (partition by custid,custdate,datasourceid) order by id desc  as rn
from yourtable
)
select custdate,max(custgrp1currency) as custgrp1currency
      ,sum(custgrp1amount) as custgrp1amount
      ,max(custgrp2currency) as custgrp2currency
      ,sum(custgrp2amount) as custgrp2amount
      ,max(custovrcurrency) as custovrcurrency
      ,sum(custovramount) as custovramount
  from cte
  where rn=1
    and custid = ????
  group by custdate
  order by custdate desc

Open in new window

0
 

Author Comment

by:sbornstein2
ID: 40566239
there is no summing I need the Max(Id) record if that group
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:sbornstein2
ID: 40566240
Need the Max Id of each of those 3 datasources then need to pivot into those 7 columns.
0
 

Author Comment

by:sbornstein2
ID: 40566244
based on the max id there really should never be more than one record if any for that group so summing is not needed
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40566292
That's what the row number function does

The sum just combines the 3 rows into a single one for the pivot.

Have you tried it
0
 

Author Comment

by:sbornstein2
ID: 40566319
I will right now Lowfat thank you so much trying shortly.
0
 

Author Closing Comment

by:sbornstein2
ID: 40567263
Absolutely perfect.   Thanks LowFat huge huge help for me today thanks.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 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