Solved

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

Posted on 2014-09-30
9
186 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 54

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 54

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 54

Accepted Solution

by:
Julian Hansen earned 500 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 54

Expert Comment

by:Julian Hansen
ID: 40374021
What language?
0
 

Author Comment

by:SQL .NET
ID: 40377706
SQL
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now