How to convert transactional data(same id multiple line) to single line

Question

0.00/5 (No votes)

See more:

I have a data with same id but multiple line
i am required to convert into single line and mark id as primary key in SQL Server or excel.

Student_ID |Dept_Code |Course_Code |Status
--------------------------------------------------
1001 KP1 PHC Completed
1001 KP1 AMP Enroll
1001 KP1 CEF Not Enrolled
1001 KP1 CIP Started
1001 KP1 GEN Completed
1001 KP1 MER Not Enrolled
1001 EC2 COT Completed
1001 EC2 MER Enroll
1001 EC2 AMP Not Enrolled

Output Required:
Student_ID |Dept_Code |PHC |AMP |CEF |CIP |GEN |MER |COT
----------------------------------------------------------------------------
1001 |KP1 |Completed |Enroll |Not Enrolled |Started |Completed |Not Enrolled |NA
1001 |EC2 |NA |Not Enrolled |NA |NA |NA |Enroll |Completed

All courses code to be header and value of status to be mentioned under course code.
Courses without status can be left blank or marked as NA
Final data primary key would be Student_ID and Dept_Code

What I have tried:

I tried using case stmt in sql server but since the actual data set have about 15000 courses it is not very feasible

Posted 21-Nov-23 11:06am

GypsyLuca

Add a Solution

Comments

Mike Hankey 21-Nov-23 19:15pm

Show what you have tried and where you are stuck.
We did our homework years ago now it's your turn.
We will help you if you help you.

GypsyLuca 23-Nov-23 17:47pm

Hi Mike,
I wish this was my Homework. Unfortunately this happens to be my Actual work.
I work as an Admin/Data Entry for a Technical Institute.
Thanks for your time.

[no name] 22-Nov-23 1:04am

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

GypsyLuca 23-Nov-23 17:50pm

Thanks Gerry.
My Final query was using Pivot.
Thanks for teaching me something new.
I am very new to SQL. Currently employed as an Admin/Data entry.
Thanks again for the link

Mike Hankey 23-Nov-23 17:58pm

My apologies, we get so many that want us to do their homework.

GypsyLuca 23-Nov-23 18:28pm

None taken mate.
But I wish 20+ years back ,if I knew of forums which we could get help for our homework done.
we have come long way.
Thanks again.

2 solutions

Solution 1

Quote:
since the actual data set have about 15000 courses it is not very feasible

That means you will end up with a table with about 15002 columns - one for each course, and one each for the student ID and department code.

As per the Maximum capacity specifications for SQL Server[^], a table may contain a maximum of 1024 columns. (That increases to 30000 if you're using sparse columns[^]; but I suspect you'd still be limited as to how many of the columns could have a value in a single row.)

Either way, such a horrendously wide table would be a nightmare to work with - apart from anything else, you can't have more than 4096 columns in a single SELECT or INSERT statement.

Your desired table structure is an extreme example of denormalization. You will almost certainly be better served by keeping the data in its current normalized form, and producing the denormalized output for a subset of the courses on demand.

Posted 21-Nov-23 22:25pm

Richard Deeming

Comments

GypsyLuca 23-Nov-23 17:45pm

Thanks for your reply.
I work for a Technical Institute and they have a primitive system that captures students course enrollment/status.
My line manager, every year end have to skimp through data(currently in excel) to find if how many students have met their learning plan or course credit.
unfortunately it is designed in such a way that every dept can offer every course for students to take which usually ends up same student taking same course from different dept and increasing his/her course credits.
and currently there is no rule set to not allow them to do so.
I recently joined as an admin and thought to get a database in place with my limited SQL knowledge.
and let see how it would go from here.
thanks again for your help on this.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2023-11-22T00:32:00

This is a bit interesting problem. I have created a sample solution for you that you can extend as per your need. The actual issue is 15000 courses that you can't export as columns. Practically speaking in a given date range (semester) we are sure that very limited number of courses will be used (should be in 100s) that students gets enrolled. Taking this assumption we first need to know the exact number of courses that we are talking about. We can get this information via executing a query only getting courses. Once we get this course codes, we will put this in Pivot query and get our desired result. Below is the sample solution for your review.

We will generate pre-req data

SQL

CREATE TABLE SOME_TABLE
(
	Student_ID varchar(10),
	Dept_Code varchar(10),
	Course_Code varchar(10),
	Status varchar(20)
)

INSERT INTO SOME_TABLE(Student_ID, Dept_Code, Course_Code,Status)
SELECT '1001', 'KP1', 'PHC', 'Completed'
UNION ALL
SELECT '1001', 'KP1', 'AMP', 'Enroll'
UNION ALL
SELECT '1001', 'KP1', 'CEF', 'Not Enrolled'
UNION ALL
SELECT '1001', 'KP1', 'CIP', 'Started'
UNION ALL
SELECT '1001', 'KP1', 'GEN', 'Completed'
UNION ALL
SELECT '1001', 'KP1', 'MER', 'Not Enrolled'
UNION ALL
SELECT '1001', 'EC2', 'COT', 'Completed'
UNION ALL
SELECT '1001', 'EC2', 'MER', 'Enroll'
UNION ALL
SELECT '1001', 'EC2', 'AMP', 'Not Enrolled'

Now we will get all eligible course codes

SQL

declare @dynamicCourseCode varchar(1000), @SQL varchar(2000)
select  @dynamicCourseCode = 
     
    stuff((
        select distinct ',' + Course_Code
        from SOME_TABLE
        for xml path('')
    ),1,1,'') 
from SOME_TABLE

Finally, we will put these comma-separated course code values in a dynamic sql query and execute the query

SQL

SELECT @SQL = 'SELECT *
FROM
(
  SELECT 
   Student_ID,
   Dept_Code,
   Course_Code,
   Status
  FROM SOME_TABLE
 ) AS P
 PIVOT
 (
   MAX(Status) FOR Course_Code IN (' + @dynamicCourseCode + ')
 ) AS pvt
'

EXEC (@SQL)

This will give you your desired result. There could be a query size limitation that you may face. In that case, you can move the entire logic to Stored Procedure.

Hopefuly this should solve your problem.