help on arranging numerical and varchar(max) data

Hi experts,

I have a table which holds numerical values (numerical answers to questions), and another table which contains text (verbatim answers to questions). I can easily link these tables to a table which holds customer information.

It's not a great structure, but it's what I have to work with.

Each row is one answer. Each question has an ID number and each survey has a serial number - these appear in both tables.

What I want to do is pivot the combined tables in a certain way.

For example, in the customer table I have one row for Mr Smith, SN 1234
To Question ID (QID) 5 he answers "10" and QID 11 he answers "2". For QID 5 leaves a comment of "hello world" (classic) and for QID 11 he leaves no comment.
In the 2nd table there are 2 rows, in the third table there is only one.

I want to arrange the data in such a way that the columns are:
Name | QID5 | QID5verbatim | QID11 | QID11verbatim

Open in new window

and a single row of data would be:
Mr Smith | 10 | Hello World | 2 | NULL

Open in new window



So basically, each QID row in each of the 2 answer tables is a column although slightly renamed.

I've managed to pivot the data with just the first 2 tables (customer info and numerical answers), but struggling on the final stage of this.
LVL 5
INHOUSERESAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

coreconceptsCommented:
Good afternoon INHOUSERES,

        This is going to be quite contrived, so I apologize.  I created some sample tables called #customers, #answers_numbers and #answers_verbatum.

Customers has the customerID e.g. SN 1234, and customer Name. e.g. Mr. Smith
Answers_Numbers has the Customer_ID, the Question ID (5, or 11) and the Number answered for each
Answers Verbatum has the Customer ID, the Question ID (5, or 11) and the verbum answer 'Hello World' and NULL... if that structure sounds right, what I did was use two CTE's and a PIVOT on each, since you can't use more than one aggregate in a PIVOT I put the results of each CTE into a temp table (#mynumberstemp and #myverbatumtemp, and joined the tables on Name.

I couldn't think of another way of using two spreading columns in a single pivot.   The downfall here is that it's only ever going to show the results of question ID's you specify.. in this case 5 and 11.   With an example of your code I might be able to help more, here's mine and a screen shot of the result set:
With MyVerbatum As 
(
SELECT 
	C.Name As Name 
	, AN.Q_ID As Answer_ID
	, AV.Q_Answer_Verbatum As Answer_Verbatum
FROM #customers As C
INNER JOIN #answers_numbers As AN
	ON  AN.Customer_ID = C.ID
INNER JOIN #answers_verbatum AS AV
	ON AN.Q_ID = AV.Q_ID

)
SELECT
	Name
	, [5] As [Q_5_Verbatum]
	, [11] As [Q_11_Verbatum]
INTO #myverbatumtemp
FROM MyVerbatum
	PIVOT(MAX(Answer_Verbatum) FOR Answer_ID IN ( [5] , [11] ) )As VerbatumPivot


	;


	
With MyNumbers As 
(
SELECT 
	C.Name As Name 
	, AN.Q_ID As Answer_ID
	, AN.Q_Answer_Number As Answer_Number
FROM #customers As C
INNER JOIN #answers_numbers As AN
	ON  AN.Customer_ID = C.ID
INNER JOIN #answers_verbatum AS AV
	ON AN.Q_ID = AV.Q_ID

)
SELECT 
	Name
	, [5] As [Q_5_Number]
	, [11] As [Q_11_Number]
INTO #mynumberstemp
FROM MyNumbers
	PIVOT(MAX(Answer_Number) FOR Answer_ID IN ( [5] , [11] ) )As NumberPivot
;

SELECT MyV.Name
, MyN.Q_5_Number
, MyV.Q_5_Verbatum
, MyN.Q_11_Number
, MyV.Q_11_Verbatum
 FROM #mynumberstemp As MyN
INNER JOIN #myverbatumtemp As MyV
	ON MyN.Name = MyV.Name

Open in new window

The Result Set
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
INHOUSERES it would help us to help you if you would provide a small sample of data for each tables involved (numbers table, text table, customer table) - personal data if any may be substituted.

Ideally  using the true fieldnames and tablenames
- that way less "translation" occurs at your end for any queries we might propose.
0
INHOUSERESAuthor Commented:
Hi all,

very sorry for lack of response, stupidly busy week last week. Will look into this today or tomorrow and get back.
0
INHOUSERESAuthor Commented:
Took longer to make than I thought!

Basically, What I was trying to do was merge the tables together then do the pivot. What I should have been doing is pre-pivot all tables, then simply join them together.

Winner.

My columns are confidential along with the data, otherwise I would have been more precise, but coreconcepts comment was all I needed to get me on the right track.

Cheers for the help and thanks for the patience.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.