使用 PIVOT 和 UNPIVOT sql server

 

Select [UserID],[Username],[DisplayName],[FirstName],[LastName],[Email],
			[Address],[City],[Region],[PostalCode],[Country],[Telephone],[HowFind],[EffectiveDate]

		from 
		(
		SELECT u.UserID,		
			u.FirstName,
			u.LastName,
			u.Email,
			u.Username,
			u.DisplayName,
			up.PropertyValue,
			ppd.PropertyName,
			ur.EffectiveDate
		FROM Users u
			cross join ProfilePropertyDefinition ppd
			inner join UserProfile up on up.UserID = u.UserID and ppd.PropertyDefinitionID = up.PropertyDefinitionID
			inner JOIN UserRoles ur ON u.UserID = ur.UserID
			inner JOIN UserPortals ups ON u.UserID = ups.UserID
		WHERE
			u.UserID in (SELECT UserID from UserPortals Where PortalID = @PortalId) and
			ppd.PortalID = @PortalId and 
			ur.RoleID = (SELECT [RoleID] FROM [Roles] WHERE RoleName='Restricted User' and PortalID = @PortalId)

		
		) t
	pivot 
	(
		max(PropertyValue)
		for PropertyName in ([Address],[City],[Region],[PostalCode],[Country],[Telephone],[HowFind]) 
	) pvt 
ORDER BY [UserID]

 

参考：http://msdn.microsoft.com/zh-cn/library/ms177410(v=SQL.100).aspx