SQL query to group results on matching record ID

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in the table that match each input person’s date of birth and gender, aggregated by the InputPersonId, so each returned record would have:

Convert 7 columns in two rows to 14 columns in one row

I need a T-SQL script to change two rows to one row. The rows are grouped by Student ID and Unique Course Identifier. The second row columns in the groups for the Days, Start Time, End Time, Start Date, End Date, Building and Room need to be on the first row in the columns on the right which are Days2, Start Time2, End Time2, Start Date2, End Date2, Building2 and Room2. There’s no aggregate so it doesn’t seem like I would want to use pivot. It looks like it might be possible with Union All or for xml path. What is the script for that? SQL Server 2014

SQL Server PATINDEX issue/bug when using different case-sensitive collations

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. Recently I needed to use it on a Case Sensitive collation DB and found some odd behaviors. If the %pattern% for PATINDEX is just specifying lower case (ex: %[^a-z0-9_-]% ), then all upper case “Z”s are removed when the Collation is Latin1_General_100_CS_AS. If the Collation is SQL_Latin1_General_CP1_CS_AS, then upper case “A”s are removed. Is this a bug or did I miss something?