Reg: Dynamic SQL Query from row to column in SQL server
-
Dear all
I have following scenario change the rows to column in dynamically. I have tried But not working properly. Please help me
Scenario :
IF OBJECT_ID('tempdb..#FacultyDataWithDetails') IS NOT NULL
DROP TABLE #FacultyDataWithDetails
Create table #FacultyDataWithDetails (FacultyID char(7),FacultyName varchar(100),ContactNumber varchar(20),Emailid varchar(50),Coursecode varchar(10))
insert into #FacultyDataWithDetails values('FAAAT22','Ahongsangbam Sanathoi Chanu','1234567890','chanu@gmail.com','241901002')
insert into #FacultyDataWithDetails values('FAAWM22','Sneha','1589674563','sneha@gmail.com','241901002')
insert into #FacultyDataWithDetails values('FAAKW22','varun','4568973214','varub@gmail.com','24AI01001')
insert into #FacultyDataWithDetails values('FAATV22','roshi','7894561235','roshi@gmail.com','24AI01001')
insert into #FacultyDataWithDetails values('FABIE23','sachin','9876543214','sachin@gmail.com','24AI01001')
insert into #FacultyDataWithDetails values('FABIE29','Jeeva','9873214568','jeeva@gmail.com','24AI01003')
select * from #FacultyDataWithDetails
I need following output
Thank You
- This topic was modified 8 months ago by vs.satheesh.
-
Is there a maximum number of faculty members per course code?
Also, please explain why you need to do this. This is seriously antithetical to best practices and most practical applications and, if we knew the intended use, might be able to make a better suggestion.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
;with cte as
(
select FacultyID,
FacultyName,
ContactNumber,
Emailid,
Coursecode,
ROW_NUMBER() OVER (PARTITION BY Coursecode ORDER BY (SELECT NULL)) rn
from #FacultyDataWithDetails
)
SELECT Coursecode,
MAX(CASE rn WHEN 1 THEN FacultyID END) AS Faculty1_FacultyID,
MAX(CASE rn WHEN 1 THEN FacultyName END) AS Faculty1_FacultyName,
MAX(CASE rn WHEN 1 THEN ContactNumber END) AS Faculty1_ContactNumber,
MAX(CASE rn WHEN 1 THEN Emailid END) AS Faculty1_Emailid,
MAX(CASE rn WHEN 2 THEN FacultyID END) AS Faculty2_FacultyID,
MAX(CASE rn WHEN 2 THEN FacultyName END) AS Faculty2_FacultyName,
MAX(CASE rn WHEN 2 THEN ContactNumber END) AS Faculty2_ContactNumber,
MAX(CASE rn WHEN 2 THEN Emailid END) AS Faculty2_Emailid,
MAX(CASE rn WHEN 3 THEN FacultyID END) AS Faculty3_FacultyID,
MAX(CASE rn WHEN 3 THEN FacultyName END) AS Faculty3_FacultyName,
MAX(CASE rn WHEN 3 THEN ContactNumber END) AS Faculty3_ContactNumber,
MAX(CASE rn WHEN 3 THEN Emailid END) AS Faculty3_Emailid
FROM CTE
GROUP BY Coursecode
; -
Hello,
Thank you for your kind response
In this scenario, the goal is to generate a report according to client requirements without using any reporting tools. The query output is directly rendered into an Excel file.
My requirement is each course code number of faculties not mentioned maximum limit, But out our convenience we can define maximum 20 faculties . Its vary from each course code.
Regards
v.s.satheesh
-
Hi
Thank you for your response.
In my scenario faculty details is dynamic for each course code . Example purpose I have mentioned three course codes.Sorry previously I am not mentioned this condition.
Regards
v.s.satheesh
-
Did you try Jonathan's code?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Did you try Jonathan's code?
Jonathan's code works only for 3 falculty members. The op will "settle" for up to 20.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - vs.satheesh wrote:
Hello,
Thank you for your kind response
In this scenario, the goal is to generate a report according to client requirements without using any reporting tools. The query output is directly rendered into an Excel file.
My requirement is each course code number of faculties not mentioned maximum limit, But out our convenience we can define maximum 20 faculties . Its vary from each course code.
Regards
v.s.satheesh
It would really be nice to know why the "client" wants to make such an awful mistake but this is also why most people don't allow "self-service" users.
That, not withstanding and using Jonathan's good code for a primary working example, please modify his code to be dynamic using the method covered in the following article. One of us would write the code for you but this kind of a request is quite common and you need to learn how to do it for yourself... It's called a "Dynamic Crosstab" or a "Dynamic Pivot".
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Phil Parkin wrote:
Did you try Jonathan's code?
Jonathan's code works only for 3 falculty members. The op will "settle" for up to 20.
At no point did I suggest that Jonathan's code was the complete solution, as well you know! A few minutes of tedious cutting and pasting would probably get it done, however. Your follow-up post is spot on. All in all, seems like a really weird request from someone who doesn't really know what they want.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply