Solved

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

Posted on 2014-09-30
9
183 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
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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
Comment Utility
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
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:SQL .NET
Comment Utility
Yes Absolutely
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
Comment Utility
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
Comment Utility
Can anyone please given me an example or link where I can achive my results.
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
What language?
0
 

Author Comment

by:SQL .NET
Comment Utility
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.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

11 Experts available now in Live!

Get 1:1 Help Now