Reg: Dynamic SQL Query from row to column in SQL server

Question

Reg: Dynamic SQL Query from row to column in SQL server

vs.satheesh

SSCrazy

Points: 2392
More actions
March 26, 2024 at 7:26 am

#4402259
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 1 month ago by vs.satheesh.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

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)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

;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
;

vs.satheesh SSCrazy Points: 2392 More actions · Answer 3

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

vs.satheesh SSCrazy Points: 2392 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246697 More actions · Answer 5

Did you try Jonathan's code?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 7

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)

Phil Parkin SSC Guru Points: 246697 More actions · Answer 8

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.