Need to join rows(table1) with columns(table2)

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

SQL Server Development (2000)

Need to join rows(table1) with columns(table2)

Author

Topic

gsolares
Starting Member

7 Posts

Posted - 2008-10-06 : 15:13:13

I might be doing the wrong thing altogether, there must be a simple way to do what I want to, so please feel free to point that out.

Simplified I have a table with a questionnaire:
Fields: number, question
Example:
number question
1 what
2 where
3 when

And another table with users responses
Fields: userID, what, where, when
Example
userID what where when
001 party home friday

What I would like to get from a query is:

For userID=001
number question answer
1 what party
2 where home
3 when friday

Any ideas will be greatly appreciated.

Cheers,
GS

Thanks,

GS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:17:06

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 15:17:59

Or this abridged version
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

gsolares
Starting Member

7 Posts

Posted - 2008-10-06 : 16:10:49

Sorry, here is the full info...

CREATE TABLE [dbo].[Questionnaire] (
[Number] [int] IDENTITY (1, 1) NOT NULL ,
[Question] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QuestionID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Questionnaire(Question, QuestionID)
SELECT 'What', 'ID01' UNION ALL
SELECT 'Where', 'ID02' UNION ALL
SELECT 'When', 'ID03'

CREATE TABLE [dbo].[Q_Data] (
[UserID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID01] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID02] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID03] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Q_Data(UserID, ID01, ID02, ID03)
SELECT 'User01', 'Party', 'Here', 'Now' UNION ALL
SELECT 'User02', 'Work', 'There', 'Tomorrow'

I need to create a query to get the following result for User01:

Number Question Answer
1 What Party
2 Where Here
3 When Now

Hope this is a little better.

Thanks,

GS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 16:19:37

[code]DECLARE @Questionnaire TABLE(
[Number] [int] IDENTITY (1, 1) NOT NULL ,
[Question] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QuestionID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

INSERT INTO @Questionnaire(Question, QuestionID)
SELECT 'What', 'ID01' UNION ALL
SELECT 'Where', 'ID02' UNION ALL
SELECT 'When', 'ID03'

DECLARE @Q_Data TABLE (
[UserID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID01] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID02] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ID03] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

INSERT INTO @Q_Data(UserID, ID01, ID02, ID03)
SELECT 'User01', 'Party', 'Here', 'Now' UNION ALL
SELECT 'User02', 'Work', 'There', 'Tomorrow'

SELECT q.number,
q.question,
x.answer
FROM @Questionnaire AS q
LEFT JOIN (
SELECT 'ID01' AS col,
ID01 AS answer
FROM @Q_Data
WHERE userid = 'user01'

union all

SELECT 'ID02' AS col,
ID02
FROM @Q_Data
WHERE userid = 'user01'

union all
SELECT 'ID03' AS col,
ID03
FROM @Q_Data
WHERE userid = 'user01'
) AS x ON x.col = q.QuestionID[/code]

E 12°55'05.63"
N 56°04'39.26"

gsolares
Starting Member

7 Posts

Posted - 2008-10-06 : 16:27:28

Brilliant, thanks a lot!

Thanks,

GS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 16:29:13

9 minutes is not that bad, right?

What will you do next time you have a question?