Need help rotating a table

Need help rotating a table

• Tom2

  That definitely looks quite a bit more elegant than what I posted.

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Mark Benvenuto@discussions..microsoft.com> wrote in message news:772d0e3f-b429-4b9c-b4bd-657eb79b5e46@discussions.microsoft.com...

  Another version
  
  select * from
  students s, (
  select studentid, English, Spanish, Math
  from studentsubject
  pivot
  (
  count(subject) for Subject in (English, Spanish, Math)
  ) as pvt ) as ss where s.studentid = ss.studentid

  
  

• Wang Chi

  Here's an example:

   

   

  CREATE TABLE #Students
  (
      StudentId INT,
      StudentName VARCHAR(20)
  )
  GO

   

  CREATE TABLE #StudentSubjects
  (
      StudentId INT,
      Subject VARCHAR(20)
  )
  GO

   

  INSERT #Students
  SELECT 1, 'John Doe'
  UNION ALL
  SELECT 2, 'Jane Doe'

   

  INSERT #StudentSubjects
  SELECT 1, 'English'
  UNION ALL
  SELECT 1, 'Math'
  UNION ALL
  SELECT 2, 'English'
  UNION ALL
  SELECT 2, 'Spanish'
  GO

   

  SELECT
      StudentId,
      StudentName,
      CASE
          WHEN [English] IS NULL THEN 0
          ELSE 1
      END AS English,
      CASE
          WHEN [Math] IS NULL THEN 0
          ELSE 1
      END AS Math,
      CASE
          WHEN [Spanish] IS NULL THEN 0
          ELSE 1
      END AS Spanish
  FROM
  (
  SELECT
      S.StudentId,
      S.StudentName,
      SS.Subject
  FROM #Students S
  JOIN #StudentSubjects SS ON S.StudentId = SS.StudentId
  ) x
  PIVOT
  (
      MIN(Subject)
      FOR Subject IN ([English], [Math], [Spanish])
  ) p
  GO

   

  DROP TABLE #Students
  DROP TABLE #StudentSubjects
  GO

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  <Yogesh Prabhu@discussions.microsoft.com> wrote in message news:65d470d2-c338-47e7-92dc-dd349095aa81@discussions.microsoft.com...

  I'm using SQL Server 2005. I have a situation similar to described here. Let's say there's a Student table and StudentSubject table.

  Student table

  StudentID int IDENTITY(1,1) NOT NULL

  StudentName varchar(50) NOT NULL

  StudentSubject table

  StudentID INT NOT NULL

  Subject varchar(10) NOT NULL

  Sample Data of Student Table

  StudentID StudentName

  1 Jane Doe

  2 John Doe

  Sample data of StudentSubject table

  StudentID Subject

  1 English

  1 Math

  2 English

  2 Spanish

  My question is how I can turn the table so that my resultset could look like:

  StudentID StudentName English Spanish Math

  1 Jane Doe 1 0 1

  2 John Doe 1 1 0

  I'm having hard time using PIVOTing techniques. I thought of one solution that used CASE..wHEN constructs, but is there a elegant solution

  Thanks

  
  

• Mikhail Arkhipov

  Another version
  
  select * from
  students s, (
  select studentid, English, Spanish, Math
  from studentsubject
  pivot
  (
  count(subject) for Subject in (English, Spanish, Math)
  ) as pvt ) as ss where s.studentid = ss.studentid
  
  
• Need help rotating a table