November 30, 2015 at 11:19 am
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](256) 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
leftJOIN 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?
November 30, 2015 at 11:43 am
A cross tab query should work well for this sort of thing.
Something like this (using just a subset of the columns in the real table for sample data):
CREATE TABLE #CustomStudent
(
personID INT ,
attributeID INT ,
value VARCHAR(256)
);
INSERT INTO #CustomStudent
VALUES ( 1, 272, 'I''m some 272 value!' ),
( 1, 273, 'I''m some 273 value!' ),
( 1, 274, 'I''m some 274 value!' ),
( 1, 277, 'I''m some 277 value!' ),
( 2, 891, 'I''m some 891 value!' );
SELECT personID ,
CS272 = MAX(CASE WHEN attributeID = 272 THEN value
ELSE NULL
END) ,
CS273 = MAX(CASE WHEN attributeID = 273 THEN value
ELSE NULL
END) ,
CS274 = MAX(CASE WHEN attributeID = 274 THEN value
ELSE NULL
END) ,
CS275 = MAX(CASE WHEN attributeID = 275 THEN value
ELSE NULL
END) ,
CS276 = MAX(CASE WHEN attributeID = 276 THEN value
ELSE NULL
END) ,
CS277 = MAX(CASE WHEN attributeID = 277 THEN value
ELSE NULL
END) ,
CS891 = MAX(CASE WHEN attributeID = 891 THEN value
ELSE NULL
END)
FROM #CustomStudent
GROUP BY personID;
DROP TABLE #CustomStudent;
You can add a WHERE clause if you're just wanting to return a particular personID.
Cheers!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply