Geek Answers Handbook

SQL Server - Possible Consolidated Solution?

I have a fairly simple problem that is surprisingly hard to find on the Internet. Maybe I'm looking for the wrong keywords, so I wanted to stop and ask you guys because your site was a blessing in my research. See below scenario:

Select the student, the counter (*) as Total (unknown variable: book1, book2, book3, book4, ect ...) from the mysteries.

Essentially, all I would like to do is list all the books for the student unique identifier that corresponds to the Total account. Can someone point me in the right direction, a good read, or something else so that I can take a step in the right direction? I assume that this will be done through the left connection (not sure how to make part x1, x2, x3), and then just link them by the student's unique number (no duplicates), but all online points are for turning, but turning point appears put all rows in columns instead of a single column. SQL Server 2005 is the platform of choice.

Thanks!

Unfortunately

The following query creates my unique identifier (student) and student for all duplicate entries in the table:

select student, count(*) as Total from mystudies group by student order by total desc

I don't know how to create left join in unique id table (boookid)

 select mystudies1.student, mystudies1.total, mystudies2.bookid from ( select student, count(*) as Total from mystudies group by student ) mystudies1 left join ( select student, bookid from mystudies ) mystudies2 on mystudies1.student=mystudies2.student order by mystudies1.total desc, mystudies1.student asc

Obviously, the above line will produce results similar to the following:

 Student Total BookID 000001 3 100001 000001 3 100002 000001 3 100003 000002 2 200001 000002 2 200002 000003 1 300001

But what I really want is like the following:

 Student Total BookID 000001 3 100001, 100002, 100003 000002 2 200001, 200002 000003 1 300001

I suggested that this should be done in the left junction so that it does not change the actual score performed by the student. thanks!

+1
source share
1 answer

SQL-Server uses the FOR XML path method:

 SELECT Student, Total, STUFF(( SELECT ', ' + BookID FROM MyStudies books WHERE Books.Student = MyStudies.Student FOR XML PATH(''), TYPE ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Books FROM ( SELECT Student, COUNT(*) AS Total FROM myStudies GROUP BY Student ) MyStudies

I previously gave a complete explanation of how the XML PATH method works here . With further improvement, my answer was indicated here

SQL Server Fiddle

In MySQL AND SQLite, you can use the GROUP_CONCAT function:

 SELECT Student, COUNT(*) AS Total, GROUP_CONCAT(BookID) AS Books FROM myStudies GROUP BY Student

MySQL Fiddle

SQLite Fiddle

In Postgresql, you can use the ARRAY_AGG Function:

 SELECT Student, COUNT(*) AS Total, ARRAY_AGG(BookID) AS Books FROM myStudies GROUP BY Student

Postgresql fiddle

In oracle you can use LISTAGG Function

 SELECT Student, COUNT(*) AS Total, LISTAGG(BookID, ', ') WITHIN GROUP (ORDER BY BookID) AS Books FROM myStudies GROUP BY Student

Oracle SQL Fiddle

+3
source

More articles:


All Articles