-1

I am using SQL Server 2014.

Student databse:

  • notlar (@vize, @ders_id, @ogrenci_id)
  • ogrenciler (@id, @isim)
  • dersler (@ders_adi, @ders_id)

There are tables and columns in the form of the student's visa notes with the cursor and the course is listed in the course name.

The output is as follows:

Select 
    o1.adi , n1.vize, d1.ders_adi 
from 
    ogrenci o1, notlar n1, dersler d1 
where 
    o1.Ogr_ID = n1.Ogr_ID 
    and convert(varchar(22), n1.Ders_Kod) = convert(varchar(22), d1.Ders_Kod)  
order by 
    adi

DECLARE @adi NVARCHAR(MAX), @vize NVARCHAR(MAX), @ders NVARCHAR(MAX) 

DECLARE CRS_bolumler CURSOR FOR
    SELECT 
        o1.adi , n1.vize, d1.ders_adi 
    FROM
        ogrenci o1, notlar n1, dersler d1 
    WHERE
        o1.Ogr_ID = n1.Ogr_ID 
        AND CONVERT(VARCHAR(22), n1.Ders_Kod) = CONVERT(VARCHAR(22), d1.Ders_Kod) 
    ORDER BY
        adi

OPEN CRS_bolumler

FETCH NEXT FROM CRS_bolumler INTO @adi, @vize, @ders 

WHILE @@FETCH_STATUS =0
BEGIN   
    PRINT @adi + ',' + @vize + ',' + @ders

    FETCH NEXT FROM CRS_bolumler INTO @adi, @vize,@ders
END

CLOSE CRS_bolumler 
DEALLOCATE CRS_bolumler

Messages:

 {Ali,60,Bilg. Müh. Giriş    
    Ali,50,Alg ve Prog-I       
    Ali,40,Matematik-I         
    Betül,30,Bilg. Müh. Giriş    
    Betül,30,Alg ve Prog-I       
    Betül,20,Matematik-I         
    Bilal,70,Bilg. Müh. Giriş    
    Bilal,80,Alg ve Prog-I       
    Bilal,90,Matematik-I         
    Feyza,80,Bilg. Müh. Giriş    
    Feyza,60,Alg ve Prog-I       
    Feyza,40,Matematik-I         
    Leyla,20,Bilg. Müh. Giriş    
    Leyla,30,Alg ve Prog-I       
    Leyla,20,Matematik-I }

I'm getting a printout. My request is to get the visa name and course name of the students with the same names as follows:

{Ali,60,Bilg. Müh. Giriş ,50,Alg ve Prog-I   ,40,Matematik-I         
Betül,30,Bilg. Müh. Giriş ,30,Alg ve Prog-I ,20,Matematik-I         
Bilal,70,Bilg. Müh. Giriş ,80,Alg ve Prog-I  ,90,Matematik-I         
Feyza,80,Bilg. Müh. Giriş  ,60,Alg ve Prog-I ,40,Matematik-I         
Leyla,20,Bilg. Müh. Giriş,30,Alg ve Prog-I ,20,Matematik-I }
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Tuna Yangir
  • 3
  • 4
  • 3
    Look up how to use joins in SQL – Ben Jan 10 '19 at 21:27
  • I searched but could not find – Tuna Yangir Jan 10 '19 at 21:29
  • Looks more like you need a pivot? Rather tough to tell considering I have no idea what language this is in.https://stackoverflow.com/questions/54135039/flatten-table-rows-into-columns-in-sql-server – Steve-o169 Jan 10 '19 at 21:30
  • 1
    You couldn't find anything for SQL and Join? https://www.w3schools.com/sql/sql_join.asp – Ben Jan 10 '19 at 21:33
  • 1
    The way you are joining the tables is deprecated and considered a bad habit. Please, see [SQL JOIN: is there a difference between USING, ON or WHERE?](https://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where) – Angel M. Jan 10 '19 at 21:33
  • I want to combine in one column – Tuna Yangir Jan 10 '19 at 21:35
  • 1
    Additionally, CURSORS need to be prevented if possible as they decrease drastically the performance and may create unnecessary LOCKS. – Angel M. Jan 10 '19 at 21:36
  • Could you please show the DDL and expected results in a clearer way? I still don't have the image of what you want to achieve. – Angel M. Jan 10 '19 at 21:37
  • the output I received in the message section. I want to print this way: {Ali,60,Bilg. Müh. Giriş ,50,Alg ve Prog-I ,40,Matematik-I Betül,30,Bilg. Müh. Giriş ,30,Alg ve Prog-I ,20,Matematik-I Bilal,70,Bilg. Müh. Giriş ,80,Alg ve Prog-I ,90,Matematik-I Feyza,80,Bilg. Müh. Giriş ,60,Alg ve Prog-I ,40,Matematik-I Leyla,20,Bilg. Müh. Giriş,30,Alg ve Prog-I ,20,Matematik-I } – Tuna Yangir Jan 10 '19 at 21:42
  • As I said, it's basically a pivot... Each student has 3 rows with an integer and class in each. He wants one row per student with all integers and classes associated with that student's name. – Steve-o169 Jan 10 '19 at 21:44
  • not pivot solution – Tuna Yangir Jan 10 '19 at 21:57

1 Answers1

0

Solution of the problem:

select o.isim, 
       stuff((
            select ', ' + ltrim(str(n.vize)) + ', ' + d.ders_adi
            from notlar n
            inner join dersler d on n.ders_id = d.ders_id
            where n.ogrenci_id = o.id
            FOR XML PATH('')
            ), 1, 2, '') as vizeler
from ogrenciler o;
Tuna Yangir
  • 3
  • 4