getting row from one table and inserting it into another table as column

Test  Question  Domain  Competency "Answer"
231              1           2                    6             4  (First Record in the row)
231              2            2                    7             3 (Second Record in the row)
So I wanna get the data from that row and  add that row to another Table as column. Consider the record from ans1  and now i want to put this data into the column as record for ID 1 then ans2 will be the record for ID2 row for that column.We can identify the column by test number(better option) as the row is the answer for that test number .Basically place entire row as a separate column  in another table. and I have to do it for 30 rows. SO we will add 30 columns in second table(Second Image) . Please guide. A direction or 2 or 3 helpful commands will suffice as an answer
SQL .NETAsked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Unpivot the table with one row and many columns. This gives you the two columns and many rows.

Then join that table with your other table.

You should use a CTE to first unpivot, then do the join.

Hope that helps.
Julian HansenCommented:
Just to clarify - do you want to add one column for each row of answers? If so I am confused by the Test Column shown in the second image that has a value 231 - how is that related to the data?
SQL .NETAuthor Commented:
C.PNGI want my data like this
I have 30 rows in different table

Relation of Data :
231 is test number Which has 80 Questions. Now the answer for these questions is stored in another table in one row as you can see in the first image of my question or in the image below. The column name name signifies the ans for respective question. So actually 80 columns for the 1 row as it is specifically for the test 231.
Here is actual table rows are more than 50 so cant fit.
so lets  say I put  Row foe test 231 in the answers column for test 231. Now after copying the 80 rows in that column from 1 to 80 the 81st element in that column will be for another test (say 194) so again 80 rows will be copied for that column so in this way the column data will be 30*80 =2400 so last element of the column will be 2400.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Julian HansenCommented:
Just trying to ensure I understand what you are trying to do - from your earlier post it seemed like you wanted answers for 231 to be in its own column and then the answers for 194 again to be in its own column to the right of those for 231.

If I understand you correctly there is only one additional column "Answers" in your Test table which will hold the transposed row of answers for that question so
231    1     2    6    4
231    2     2    7    2
231   80    3     1    5 
194    1    1     2    2
194    2    2     4    4

Open in new window

Is that correct?
SQL .NETAuthor Commented:
Yes Absolutely
Julian HansenCommented:
You could do this with a stored procedure (Which is scripting in a way) or through a complex and ugly set of joins.

Personally - I would script this.

Write a small script to loop through the questions and find the corresponding answer for it and add it to the database.

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
SQL .NETAuthor Commented:
Can anyone please given me an example or link where I can achive my results.
Julian HansenCommented:
What language?
SQL .NETAuthor Commented:
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
MySQL Server

From novice to tech pro — start learning today.