Select Query that Maps the Column Names from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Query that Maps the Column Names from table

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-29 : 15:34:17

This seems a little complicated to explain but here goes:

I have a table that holds questionnaire results with column names like Q1a, Q1b, Q1c etc. This table is used for multiple questionnaires of the same format so the questions can vary so I have another table that holds the actual questions with a column whose values matches the other tables column names(if that makes sense). Here is the scripts which will hopefully clarify this part:

CREATE TABLE [dbo].[tbl_QuestionnaireNP] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[URN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Product] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RegusCentre] [int] NULL ,
[Email] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndustrySector] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoOfEmployees] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q1a] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q1b] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q1c] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q2a] [bit] NOT NULL ,
[Q2b] [bit] NOT NULL ,
[Q2c] [bit] NOT NULL ,
[Q2d] [bit] NOT NULL ,
[Q2e] [bit] NOT NULL ,
[Q2f] [bit] NOT NULL ,
[Q2Extra] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q3] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q3Extra] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q4] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Q5a] [bit] NULL ,
[Q5b] [bit] NULL ,
[Q5c] [bit] NULL ,
[Q5d] [bit] NULL ,
[Q5Extra] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cycle] [int] NULL ,
[Actioned] [bit] NULL ,
[ActionTaken] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActionedDate] [smalldatetime] NULL ,
[ActionComments] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_questions] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[number] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[question] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cycle] [int] NULL ,
[product] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Now what I need to do is write a query that will get the question text (e.g. tbl_questions.question) for each colum in my select query in place of the tbl_QuestionnaireNP column name. I know this is not the correct syntax but basically my query would be something like follows:

SELECT Q1a AS [Why dd we lose your business], Q1b AS [What could have done to improve]

I hope this makes at least some sense to someone that may be able to help.

Thanks in advance.

Simon

www.creativenrg.co.uk

Tahsin
Starting Member

34 Posts

Posted - 2006-03-29 : 15:51:50

Are you looking for something along these lines:

SELECT Q1a AS [Why dd we lose your business], Q1b AS [What could have done to improve] FROM tbl_questionnaireNP A
INNER JOIN tbl_questions B ON A.id = B.id

If not, could you post some sample data with expected results?

- Tahsin

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-29 : 16:30:51

Not really what I was looking for but probably a fault of my explanation. Where I had put [Why dd we lose your business], this value needs to come from tbl_questions table so:

SELECT tbl_QuestionnaireNP.Q1a AS [(SELECT question FROM tbl_questions WHERE number = ** THIS COLUMN NAME e.g. Q1a **)]

www.creativenrg.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources