SQL Creating one table from two, not union or join

I'm new to SQL but so far I haven't been able to find anything on this, so maybe it's just impossible but I figured I'd ask.

I have two tables and I want to create a开发者_JAVA百科 table using one as a template and adding elements of the other to new columns that reflect the row information of the second table. All of this matched by a record ID. I'm not sure if I'm describing it well so I hope the example helps.

The two tables would look like

Record ID... Owner.....Customer

.......1............Tim...........HL......

.......2............Jon...........JP......

.......3............Evan.........JP......

Record ID... Question #.....Question Response

.......1............65...........Y......

.......1............68...........N......

.......2............65...........Y......

.......2............68...........Y......

.......3............68...........N......

And the new table would look like:

Record ID... Owner.....Customer..Question 65...Question 68

.......1............Tim...........HL...............Y....................N

.......2............Jon...........JP...............Y.....................Y

.......3............Evan.........JP............NULL.................N

---

I think you're looking for pivot. Check here for details:

http://msdn.microsoft.com/en-us/library/ms177410.aspx
http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

---

If you have only Question65 and Question68 then you can use the below mentiond query. if you have starting from Question1 and all the way to Question65, 66, 67... then you are better off doing it in your application or pivots or something else.

INSERT INTO NewTable
SELECT t1.RecordID, t1.owner, t1.Customer, t2.Question65, t2.Question68
FROM table1 t1 LEFT JOIN (
       SELECT q1.RecordID, q1.QuestionResponse AS Question65,
            q2.QuestionResponse AS Question68
       FROM Question q1 LEFT JOIN Question q2 ON 
                   q1.RecordID = q2.RecordID and q2.Question# = 68
       WHERE q1.Question# = 65 
   ) t2 
ON t1.RecordID = t2.RecordID

---

That sort of thing is really, REALLY better done in application code. However if you are insistent upon doing it with SQL, look up Pivot Tables. Some sample pages:

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

http://www.bennadel.com/blog/496-SQL-Pivot-Tables-Are-Rockin-Awesome-Thanks-Rick-Osborne-.htm