Need Help with SQL
-
Saturday, February 16, 2013 1:36 AM
Hi All i have a table like this
CASEID AMNT TYPEID EJK221 1000 1 1500 1 500 2 1100 2 1250 2 5689 3 2478 3 845 1 658 2
I want the table to look like:
CASEID AMNT [1] [2] [3] EJK221 1000 1000 500 5689 1500 1500 1100 2478 500 845 1250 1100 658 1250 5689 2478 845 658
For this i used a PIVOT sql and almost acheived it,
SELECT loannumber, clientname, receivableamount, [1] AS PrincipalId, [2] AS InterestId, [3] AS FeeId FROM (SELECT DISTINCT t1.loannumber, t6.clientname, t5.receivabletypeid, t5.receivableadddate, t5.receivableamount, Row_number() OVER( partition BY t1.loannumber, t5.receivabletypeid ORDER BY t5.receivabletypeid) AS rn FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.projectid = t2.projectid LEFT OUTER JOIN table3 t3 ON t1.loanid = t3.accountid LEFT OUTER JOIN table4 t4 ON t3.transactionid = t4.transactionid LEFT OUTER JOIN table5 t5 ON t4.receivableid = t5.receivableid LEFT OUTER JOIN table6 t6 ON t2.clientid = t6.clientid WHERE t5.receivabletypeid IS NOT NULL AND t1.loannumber = 'EJK221') p PIVOT ( Count(rn) FOR receivabletypeid IN ([1], [2], [3]) ) AS pvtBut all i could get was
CASEID AMNT [1] [2] [3] EJK221 1000 1000 NULL NULL 1500 1500 NULL NULL 500 NULL 500 NULL 1100 NULL 1100 NULL 1250 NULL 1250 NULL 5689 NULL NULL 5689 2478 NULL NULL 2478 845 845 NULL NULL 658 NULL 658 NULL
Any advices or ideas would be great
Novice
All Replies
-
Saturday, February 16, 2013 3:41 AMModerator
You may try pivoting it with CASE expression:
http://www.sqlusa.com/bestpractices/training/scripts/casefunction/
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
Saturday, February 16, 2013 6:29 AMplease provide table structure with some sampledata..
Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh
-
Saturday, February 16, 2013 4:44 PMthanks Kalman but thats not giving the desired results.
Novice
-
Saturday, February 16, 2013 5:30 PMKapil do you want sample data from all the tables in the JOIN? or just sample data from the end result.
Novice
-
Saturday, February 16, 2013 6:44 PM
Kapil do you want sample data from all the tables in the JOIN? or just sample data from the end result.
Sample create tables (table1....table6) script and with sample data in those will help.
Novice
Narsimha
-
Sunday, February 17, 2013 12:34 AM
please see the sample data
CREATE TABLE TestTable ( LOANNUMBER VARCHAR(10), CLIENTNAME VARCHAR(10),RECEIVABLEAMOUNT NUMERIC,PRINCIPALID INT, INTERESTID INT,FEEID INT ----INSERT INTO TestTable using SELECT INSERT INTO TestTable (LOANNUMBER , CLIENTNAME ,RECEIVABLEAMOUNT ,PRINCIPALID , INTERESTID ,FEEID ) SELECT ,ARRA 21-10','Town of Boones Mill','6040.57,'0.00','0.00' union all select 'ARRA 21-10','Town of Boones Mill','6114.19','0.00','0.00' union all select 'ARRA 21-10','Town of Boones Mill','6189.40','0.00','0.00' union all select 'ARRA 21-10','Town of Boones Mill','6265.53','0.00','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','2759.94','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','2820.60','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','2880.48','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','2938.64','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','3028.08','0.00' union all select 'ARRA 21-10','Town of Boones Mill','0.00','0.00','704.04' union all select 'ARRA 21-10','Town of Boones Mill','0.00','0.00','719.52' union all select 'ARRA 21-10','Town of Boones Mill','0.00','0.00','734.82' union all select 'ARRA 21-10','Town of Boones Mill','0.00','0.00','749.68' union all select 'ARRA 21-10','Town of Boones Mill','0.00','0.00','772.45'
Novice
-
Sunday, February 17, 2013 4:01 AMModerator
Try:
;with cte as (select CaseId, Amnt, TypeId,
row_number() over (partition by CaseId, TypeId order by Amnt) as Rn, Amnt as Dummy from myTable) select CaseId, Amnt, [1],[2],[3] from cte PIVOT (max(Dummy) for TypeID IN ([1],[2],[3])) pvt
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Sunday, February 17, 2013 3:43 PM
Loannumber client principal interest fee ARRA 21-10 Town of Boones Mill 6040.57 NULL NULL ARRA 21-10 Town of Boones Mill NULL 2938.64 NULL ARRA 21-10 Town of Boones Mill NULL 3028.08 772.45 ARRA 21-10 Town of Boones Mill NULL 2759.94 704.04 ARRA 21-10 Town of Boones Mill 6114.19 2820.6 719.52 ARRA 21-10 Town of Boones Mill 6189.4 2880.48 734.82 ARRA 21-10 Town of Boones Mill 6265.53 NULL 749.68
Thanks Naomi a lot,
I'm getting the results as desired but how should i replace the NULLS in the following dataset with '0' as i wanted to sum the three columns. I tried case but got an error saying cannot conver VARCHAR to NUMERIC and when i tried CAST or CONVERTfor some reason its not converting it.
Novice
-
Sunday, February 17, 2013 4:20 PMModerator
Perhaps,
with cte as (select CaseId, Amnt, TypeId, row_number() over (partition by CaseId, TypeId order by Amnt) as Rn, Amnt as Dummy from myTable) select CaseId, Amnt, ISNULL([1],0) as [1], ISNULL([2],0) as [2], ISNULL([3],0) as [3] from cte PIVOT (max(Dummy) for TypeID IN ([1],[2],[3])) pvt
if you're using the query (or idea) of what I suggested and you're using numerical values.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog
.png)
