T-sql 2012 several self joins on same table

Transact-SQL
1

In t-sql 2012, I have that I want to do alot of left joins on the same table since each row of the table has the data I want. Here is the table definition:

[dbo].[CustomStudent](
[customID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[enrollmentID] [int] NULL,
[attributeID] [int] NOT NULL,
[value] varchar NULL,
[date] [smalldatetime] NULL,
[customGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[districtID] [int] NULL)

I am trying to use sql like the following:
select distinct CS272.value,CS273.value,CS274.value
from OPS.DBO.CustomStudent AS CS272

  JOIN  OPS.DBO.CustomStudent  as CS273 
     ON  CS273.attributeID = 273   
	 and  CS272.attributeID = 272   
	 AND CS273.personID  = cs272.personID
   left	JOIN  OPS.DBO.CustomStudent  as CS274 
     ON  CS274.attributeID = 274   
	 AND CS273.personID  = cs272.personID

I want to only get one row returned. Basically I want attribute value 272 to 277, and attribute value = 891.

Thus can you show me the sql on how to get only one row returned from the query based upon personID value?

2

Rather than joining the table to itself, you can select all the rows of interest from the table and then PIVOT to get the results into one row per student. Look up the PIVOT operator which is available in SQL 2005 and later.

Alternatively, you can do the pivoting manually like shown below. The example is only for attributeid = 272 and 273, but you can extend it to others in a similar way.

SELECT CustomStudent, MAX(CASE WHEN AttributeId = 272 THEN Value END) AS ValueFor272, MAX(CASE WHEN AttributeId = 273 THEN Value END) AS ValueFor273 FROM [dbo].[CustomStudent] GROUP BY CustomStudent

3

Thanks! that answers my question.