Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-30
9
Medium Priority
?
196 Views
Last Modified: 2014-10-13
Capture2.PNGCapture.PNG
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
0
Comment
Question by:SQL .NET
  • 4
  • 4
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40352991
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.
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 40353160
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?
0
 

Author Comment

by:SQL .NET
ID: 40353314
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.
d.PNG  
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 60

Expert Comment

by:Julian Hansen
ID: 40353596
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?
0
 

Author Comment

by:SQL .NET
ID: 40358789
Yes Absolutely
0
 
LVL 60

Accepted Solution

by:
Julian Hansen earned 1500 total points
ID: 40359236
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.
0
 

Author Comment

by:SQL .NET
ID: 40373771
Can anyone please given me an example or link where I can achive my results.
0
 
LVL 60

Expert Comment

by:Julian Hansen
ID: 40374021
What language?
0
 

Author Comment

by:SQL .NET
ID: 40377706
SQL
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

916 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