Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Help partition/pivot data into rows help?

Posted on 2015-01-22
8
Medium Priority
?
93 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
  • 5
  • 2
8 Comments
 
LVL 80

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 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

927 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