locked
Pivot Without Aggregate in T-SQL RRS feed

  • Question

  • Hi, 

    When a donor makes a donation, the total amount donated is stored in the table called transaction along with transid, email etc. A donor might chose more than one fund in a single donation. So this breakdown is stored in the donation table e.g. donationid, transid, fund, amount. For each fund a new row is created in the donation table. Donor details is also stored in the donor table e.g. name, email etc. 

    I would like to convert the result of the following SQL Query (Example 1) into example 2. So if a donor donates in more than one fund the SQL query should select the each fund and amount from the donation table as a new columns instead of a new row.

    Example 1:

    Select t.transid, t.date, d.fund, d.amount, t.totalamount, dr.name
    From [Transaction] t inner join Donation d on t.transid= d.transid
    Inner join Donor dr on t.email = dr.email

    transid, Date, Fund, amount, totalamount, name
    123, 21/05/2020,Wherever Needed Most,50,110,Alex Smith
    123, 21/05/2020,Cyclone Amphan,60,110,Alex Smith
    124, 21/05/2020,Food Pack,65,65,Barak Obama
    125, 21/05/2020,Orphans,100,100,Donald Trump

    Example 2:

    /* Code */ --Please help

    Id, Date, Fund 1, Fund 1 Amount, Fund 2, Fund 2 Amount,totalamount, name
    123, 21/05/2020,Wherever Needed Most,50,Cyclone Amphan, 60,110,Alex Smith
    124, 21/05/2020,Food Pack,65,null,null,65,Barak Obama
    125, 21/05/2020,Orphans,100,null,null,100,Donald Trump

    Thanks in advance

    • Edited by Akbar11 Saturday, May 23, 2020 1:52 AM
    Saturday, May 23, 2020 1:32 AM

Answers

  • CREATE TABLE mytable(
       transid     INT  NOT NULL 
      ,Date        varchar(10)  NOT NULL
      ,Fund        VARCHAR(20) NOT NULL
      ,amount      INT  NOT NULL
      ,totalamount INT  NOT NULL
      ,name        VARCHAR(11) NOT NULL
    );
    INSERT INTO mytable(transid,Date,Fund,amount,totalamount,name) VALUES
     (123,'21/05/2020','Wherever Needed Most',50,110,'Alex Smith')
    ,(123,'21/05/2020','Cyclone Amphan',60,110,'Alex Smith')
    ,(124,'21/05/2020','Food Pack',65,65,'Barak Obama')
    ,(125,'21/05/2020','Orphans',100,100,'Barak Obama');
    
    
    
    ;with mycte as (
    select transid,Date,Fund,amount,totalamount,name
    ,row_number()  Over(partition by transid order by amount  ) rn from  mytable
    )
    
    Select transid,Date 
    ,Max(case when rn=1 then Fund else null end) Fund1
    ,Max(case when rn=1 then amount else null end) amount1
    ,Max(case when rn=2 then Fund else null end) Fund2
    ,Max(case when rn=2 then amount else null end) amount2
    ,totalamount,name
    
    from mycte
    Group by transid,Date,  totalamount,name
    
    
    drop TABLE mytable
    

    • Marked as answer by Akbar11 Monday, June 22, 2020 12:39 AM
    Saturday, May 23, 2020 1:56 AM

All replies

  • CREATE TABLE mytable(
       transid     INT  NOT NULL 
      ,Date        varchar(10)  NOT NULL
      ,Fund        VARCHAR(20) NOT NULL
      ,amount      INT  NOT NULL
      ,totalamount INT  NOT NULL
      ,name        VARCHAR(11) NOT NULL
    );
    INSERT INTO mytable(transid,Date,Fund,amount,totalamount,name) VALUES
     (123,'21/05/2020','Wherever Needed Most',50,110,'Alex Smith')
    ,(123,'21/05/2020','Cyclone Amphan',60,110,'Alex Smith')
    ,(124,'21/05/2020','Food Pack',65,65,'Barak Obama')
    ,(125,'21/05/2020','Orphans',100,100,'Barak Obama');
    
    
    
    ;with mycte as (
    select transid,Date,Fund,amount,totalamount,name
    ,row_number()  Over(partition by transid order by amount  ) rn from  mytable
    )
    
    Select transid,Date 
    ,Max(case when rn=1 then Fund else null end) Fund1
    ,Max(case when rn=1 then amount else null end) amount1
    ,Max(case when rn=2 then Fund else null end) Fund2
    ,Max(case when rn=2 then amount else null end) amount2
    ,totalamount,name
    
    from mycte
    Group by transid,Date,  totalamount,name
    
    
    drop TABLE mytable
    

    • Marked as answer by Akbar11 Monday, June 22, 2020 12:39 AM
    Saturday, May 23, 2020 1:56 AM
  • You can use the following query:

    ;with mycte as (
    Select t.transid, t.date, d.fund, d.amount, t.totalamount, dr.name
    ,row_number()  Over(partition by t.transid order by d.amount  ) rn 
    From [Transaction] t inner join Donation d on t.transid= d.transid
    Inner join Donor dr on t.email = dr.email
    )
      
    
    Select transid,Date 
    ,Max(case when rn=1 then Fund else null end) Fund1
    ,Max(case when rn=1 then amount else null end) amount1
    ,Max(case when rn=2 then Fund else null end) Fund2
    ,Max(case when rn=2 then amount else null end) amount2
    ,totalamount,name
    
    from mycte
    Group by transid,Date,  totalamount,name

    Saturday, May 23, 2020 2:25 AM
  • In case of up to three funds per donation, also try a query similar to this:

    ;
    with A as
    (
       select t.transid, t.[date], d.fund, d.amount, t.totalamount, dr.[name]
       from [Transaction] as t 
       inner join Donation as d on t.transid= d.transid
       inner join Donor as dr on t.email = dr.email
    ), 
    Q as 
    (
       select *, row_number() over (partition by transid order by amount desc) as rn 
       from A
    )
    select q1.transid, q1.[date],
       q1.Fund as [Fund1], q1.amount as [Fund1 Amount], 
       q2.Fund as [Fund2], q2.amount as [Fund2 Amount],
       q3.Fund as [Fund3], q3.amount as [Fund3 Amount],
       q1.totalamount,
       q1.[name]
    from Q as q1
    left join Q as q2 on q2.transid = q1.transid and q2.rn = 2 
    left join Q as q3 on q3.transid = q1.transid and q3.rn = 3 
    where q1.rn = 1
    order by q1.transid

    Can be adjusted and extended.





    • Edited by Viorel_MVP Saturday, May 23, 2020 8:45 AM
    Saturday, May 23, 2020 8:40 AM
  • Why did you fail to post DDL? Why don't you know the correct format for displaying a date (yyyy-mm-dd) in SQL? Why don't you know how to name a column? You also don't understand a basic principle of relational design; you don't store multiple levels of aggregation in the same table. You also seem to believe any magic Kabbalah "id" which is not part of SQL or RDBMS.

    >> When a donor makes a donation, the total amount donated is stored in the table called transaction along with transid, email etc. A donor might chose more than one fund in a single donation. <<

    The total donations should be computed in a view. We also need to know how these donations are distributed over funds. Are there any rules for this?

    CREATE TABLE Donations
    (trans_id CHAR(10) NOT NULL,
     fund_id CHAR(10) NOT NULL,
     donor_id CHAR(10) NOT NULL
        REFERENCES Donors(donor_id),
     PRIMARY KEY (trans_id, fund_id)
     donation_amount DECIMAL (10,2) NOT NULL,
     donation_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP);

    CREATE VIEW Donation_by_Funds
    AS
    SELECT fund_id, SUM(donation_amount) AS donation_total
       FROM Donations; 

    >> ... select the each fund and amount from the donation table as a new columns instead of a new row. <<

    You don't seem to understand that a column models an attribute, and not a value. You confused a relational table with some kind of spreadsheet.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, May 24, 2020 5:22 PM
  • Hi Jingyang Li, 

    That's great. Thanks for your reply. It really solves my problem. Just got one more question. There is no limit to how many funds a donor can donate to. There might be up to 20 funds he can choose from. I have seen that one donor donated to 17 funds. But usually it's 1, 2,3,5 or 7. So I don't want by default loop through 20 times e.g. 

    ;with mycte as (
    Select t.transid, t.date, d.fund, d.amount, t.totalamount, dr.name
    ,row_number()  Over(partition by t.transid order by d.amount  ) rn 
    From [Transaction] t inner join Donation d on t.transid= d.transid
    Inner join Donor dr on t.email = dr.email
    )
      
    Select transid,Date 
    ,Max(case when rn=1 then Fund else null end) Fund1
    ,Max(case when rn=1 then amount else null end) amount1
    ,Max(case when rn=2 then Fund else null end) Fund2
    ,Max(case when rn=2 then amount else null end) amount2
    ,...
    ,Max(case when rn=20 then Fund else null end) Fund20
    ,Max(case when rn=20 then amount else null end) amount20
    ,totalamount,name
    from mycte
    Group by transid,Date,  totalamount,name


    So is there way I can check what's the maximum number of funds a donor has donated first then I can loop through only that number of times? As I will be running this query every day and copying the data to a spreadsheet and every day the maximum number of funds a donor has donated in will be different. If I choose 20 funds by default this will result in columns with no data then I would have to manually delete those columns from the spreadsheet.

    So could you please suggest?

    Thanks once again. 

    • Edited by Akbar11 Monday, May 25, 2020 12:15 AM
    Monday, May 25, 2020 12:03 AM
  • You can handle up to 99 donation dynamically without change any code below.

    CREATE TABLE mytable(
       transid     INT  NOT NULL 
      ,Date        varchar(10)  NOT NULL
      ,Fund        VARCHAR(20) NOT NULL
      ,amount      INT  NOT NULL
      ,totalamount INT  NOT NULL
      ,name        VARCHAR(11) NOT NULL
    );
    INSERT INTO mytable(transid,Date,Fund,amount,totalamount,name) VALUES
     (123,'21/05/2020','Wherever Needed Most',50,110,'Alex Smith')
    ,(123,'21/05/2020','Cyclone Amphan',60,110,'Alex Smith')
    
    
    ,(124,'21/05/2020','Food Pack',65,65,'Barak Obama')
    ,(125,'21/05/2020','Orphans',100,100,'Barak Obama');
      
     
    Declare @sqlCase as NVarchar(max) =null
    Declare @sql as NVarchar(max)=null
      
       
    declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
    set @ColumnHeaders = STUFF( (SELECT   ',' + 'Max(CASE WHEN rn=' + Cast(rn as varchar(2)) + ' THEN Fund else null end ) as ' + 'Fund_'+Cast(rn as varchar(2))  
    + char(10)+char(13)  +','+ 'Max(CASE WHEN rn=' + Cast(rn as varchar(2)) + ' THEN amount else null end ) as ' +  'amount_'+Cast(rn as varchar(2)) 
    + char(10)+char(13)
    FROM (
    select transid,Date,Fund,amount,totalamount,name
    ,row_number()  Over(partition by transid order by amount  ) rn from  mytable
    ) t1
     group by rn
     order by rn
     FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
    ---print @ColumnHeaders
      
    Set @sql  =' ;with mycte as (
    select transid,Date,Fund,amount,totalamount,name
    ,row_number()  Over(partition by transid order by amount  ) rn from  mytable 
     
    )
    Select  transid,Date,  totalamount,name,'+   @ColumnHeaders + ' from mycte 
    Group by transid,Date,  totalamount,name ';
          
    --print @sql
      
    EXECUTE sp_executesql @sql 
     
     
     
     drop table mytable


    Monday, May 25, 2020 1:25 AM
  • Hi Akbar11, 

    Thank you for your issue .

    Please try Jingyang Li's script . It will use dynamic statements to solve your problems. Also , I try to use PIVOT .But it seems more difficult ,so I give up it .  CASE WHEN might be a better way . 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 25, 2020 5:45 AM