Solved

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

Posted on 2014-09-30
9
188 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 55

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 55

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 55

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 55

Expert Comment

by:Julian Hansen
ID: 40374021
What language?
0
 

Author Comment

by:SQL .NET
ID: 40377706
SQL
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Memory Keeps Increasing 4 47
SQL Error - Query 6 42
SQL Server Shrink hurting performance? 4 22
SQL Database Restore 2008 R2 1 13
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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