May 20, 2014 at 5:53 am
Hi,
I have an issue.
The concept is like, a teacher may have maximum of 4 approvers.
If teacher contains only 1 approver then the remaining columns should display empty values.
I need resulted table in the below pattern
TeacherID, TeacherName, [ApproverID at Level1], [ApproverName at Level1], [ApproverID at Level2], [ApproverName at Level2], [ApproverID at Level3], [ApproverName at Level3], [ApproverID at Level4], [ApproverName at Level4].
I have to join 3 tables to get these columns
Tables are: TeacherApprovers, Approvers, Teachers.
In TeacherApprovers Table, we have the columns like TeacherID, ApproverID, ApproverLevel
We are getting result like this:
T_ID Teacher_Name Appr_ID Approver_NAME
15122Peter Beechen1tess tsssss
15122Peter Beechen2yamini baratam
15122Peter Beechen3Moulika buragadda
16678Samantha Smith2yamini baratam
16679Matthew Shiff3Moulika buragadda
But I need result like:
T_ID Teacher_Name Appr_ID1 Approver_NAME1 Appr_ID2 Approver_NAME2 Appr_ID3 Approver_NAME3 Appr_ID4 Approver_NAME4
15122Peter Beechen1tess tsssss2yamini baratam3
Moulika buragadda NULL NULL
16678Samantha Smith2yamini baratam
16679Matthew Shiff3Moulika buragadda
Please do the needful ASAP.
May 20, 2014 at 5:56 am
We are getting result like this:
T_ID Teacher_Name Appr_ID Approver_NAME
15122 Peter Beechen 1 tess tsssss
15122 Peter Beechen 2 yamini baratam
15122 Peter Beechen 3 Moulika buragadda
16678 Samantha Smith 2 yamini baratam
16679 Matthew Shiff 3 Moulika buragadda
But I need result like:
T_ID Teacher_Name Appr_ID1 Approver_NAME1 Appr_ID2 Approver_NAME2 Appr_ID3 Approver_NAME3 Appr_ID4 Approver_NAME4
15122 Peter Beechen 1 tess tsssss 2 yamini baratam 3 Moulika buragadda NULL NULL
16678 Samantha Smith 2 yamini baratam
16679 Matthew Shiff 3 Moulika buragadda
May 20, 2014 at 6:31 am
Without the DDL to create the schema you are referring to and the DML to insert relevant data, it is very difficult to figure out a solution.
But---it sounds like you are trying to pivot data from rows into columns. Please see the following post I recently made that does very similar logic:
http://www.sqlservercentral.com/Forums/Topic1572579-391-1.aspx
If that's not enough, then post some specifics so we can work from there.
May 20, 2014 at 6:45 am
Standard pivot
;WITH cte (T_ID,Teacher_Name,Appr_ID,Approver_NAME,ColID) AS (
SELECT T_ID,Teacher_Name,Appr_ID,Approver_NAME
,ROW_NUMBER() OVER (PARTITION BY T_ID ORDER BY Appr_ID ASC)
FROM result)
SELECT T_ID,Teacher_Name
,MAX(CASE WHEN ColID = 1 THEN Appr_ID END) AS [Appr_ID1]
,MAX(CASE WHEN ColID = 1 THEN Approver_NAME END) AS [Approver_NAME1]
,MAX(CASE WHEN ColID = 2 THEN Appr_ID END) AS [Appr_ID2]
,MAX(CASE WHEN ColID = 2 THEN Approver_NAME END) AS [Approver_NAME2]
,MAX(CASE WHEN ColID = 3 THEN Appr_ID END) AS [Appr_ID3]
,MAX(CASE WHEN ColID = 3 THEN Approver_NAME END) AS [Approver_NAME3]
,MAX(CASE WHEN ColID = 4 THEN Appr_ID END) AS [Appr_ID4]
,MAX(CASE WHEN ColID = 4 THEN Approver_NAME END) AS [Approver_NAME4]
FROM cte
GROUP BY T_ID,Teacher_Name
Far away is close at hand in the images of elsewhere.
Anon.
May 20, 2014 at 7:29 am
Hi David Burrows,
Thank you so much for your reply.
Its working....................... :w00t: :hehe:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply