|
robgt
Starting Member
2 Posts |
 Posted - 2005-09-29 : 11:05:15
|
| My simplified schema is as follows:CREATE TABLE [dbo].[tblUsers] ( [UID] [int] IDENTITY (1, 1) NOT NULL , [Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DateAdded] [datetime] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblUsers_Answers] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [UID] [int] NOT NULL , [Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Answer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateAdded] [datetime] NOT NULL ) ON [PRIMARY]GOINSERT INTO tblUsers (Username) VALUES ('Bob')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABA', 'Answer 1')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABB', 'Answer 2')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABC', 'Answer 3')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABD', 'Answer 4')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABE', 'Answer 5')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABF', 'Answer 6')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABG', 'Answer 7')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABH', 'Answer 8')INSERT INTO tblUsers_Answers (UID, Code, Answer) VALUES (1, 'ABI', 'Answer 9')GOI want to create a report that can retrieve one row of data that contains Username, DateAdded from tblUsers, along with 9 other columns from tblUsers_Answers that show the answer given to the questions with a code ABA to ABI.From inserted data above, the end result should be:Bob, [date...], Answer 1, Answer 2, Answer 3, Answer 4, Answer 5, Answer 6, Answer 7, Answer 8, Answer 9Ideally, with the column name above each answer being the code that the answer is for.This would be for multiple users - not just a single user.Any help appreciated.Cheers,Rob |
|
Query to return row value as a column value