Multiple Columns From Single Cell

Running SQL2000

I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist).

This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL?





Tables, Keys, and Relationships


CREATE TABLE [dbo].[Flag] (
[FlagID] [int] NOT NULL ,
[FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StudentFlag] (
[StudentFlagID] [int] NOT NULL ,
[FlagID] [int] NULL ,
[StudentID] [int] NOT NULL ,
[FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD
CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED
(
[FlagID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD
CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED
(
[StudentFlagID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentFlag] ADD
CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY
(
[FlagID]
) REFERENCES [dbo].[Flag] (
[FlagID]
)
GO




Sample Output


StudentID, FlagType1, FlagType2, FlagType3
1, 'yes', '2', null
2, null, null, 'X'
3, null, '7', 'X'