?
Solved

Need to create a SQL 2008 crosstab/pivot table

Posted on 2012-08-29
4
Medium Priority
?
314 Views
Last Modified: 2012-09-03
I have two tables that I need to join, but the results in one table are the filed names in the other table.

Table 1 contains these two fields...
qtext
qfield

qtext is the text of a question
qfield is the actual field name of the question in another table


Table 2 contains the following fields...
case_id
survey_name
question_1
question_2
question_3

*These field names are the results of qfield in Table 1


Table 1 results:
Select qfield, qtext
From Table_1
qfield                                    qtext
question_1                             How many people are in your family?
question_2                             How old is the oldest person in your family?
question_3                             How old is the youngest person in your family?
0
Comment
Question by:swaggrK
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 38348839
I guess what you want to do is..

Case_Id            Survey_Id            Text of Question_1            Text of Question_2            Text of Question_3
CaseId1            SurveryId1            Ans11                  Ans12                  Ans13


Following is the dynamic query that could do it for you
declare @qry nvarchar(max)
set @qry = 'select case_Id, surveyname, Question_1 as ''' + (select qtext from Table_1 where qfield = 'Question_1')
                  + ''', Question_2 as ''' + (select qtext from Table_1 where qfield = 'Question_2')
                  + ''', Question_3 as ''' + (select qtext from Table_1 where qfield = 'Question_3') + ''''
                  + ' from Table_2'
exec sp_executesql @qry
0
 

Author Comment

by:swaggrK
ID: 38351329
I am getting a command completed successfully but no results.
0
 

Author Comment

by:swaggrK
ID: 38351389
Also, is there a way to do this dynamically where you do not have to type all of the associations manually.
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 750 total points
ID: 38352840
You would see results out of this query only if you have records in your table_2. Try printing the query like this
declare @qry nvarchar(max)
set @qry = 'select case_Id, surveyname, Question_1 as ''' + (select qtext from Table_1 where qfield = 'Question_1')
                  + ''', Question_2 as ''' + (select qtext from Table_1 where qfield = 'Question_2')
                  + ''', Question_3 as ''' + (select qtext from Table_1 where qfield = 'Question_3') + ''''
                  + ' from Table_2'
print @qry

Thus as you get the query printed, try running it separately in some other query window.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question