locked
Joining two tables RRS feed

  • Question

  • User954998566 posted

    Hi, 

    I would like to join my two tables by column name. 

    Here's an example of my two tables

    Table 1 : dbo.ItemQuestion

    ItemID                     q1                               q2                         q3                        q4      
    1                          24                               22                         18                        14
    
    2                           11                              31                         5                         16
    
    3                           27                              18                         44                        17
    
    4                           2                               14                          12                       15

    So I need to join the columns name  q1 ,  q2 ,   q3,   q4 

    with the second table

    Table 2 :   dbo.Questions

    QuestionCode                  Question
    
    q1                            How many gram can you hold ?
    q2                            How many years old ?
    q3                            How many screw included ?
    q4                            How many pieces included ?

    So that the select answer will be 

    ItemID   How many gram  can you hold ?  How many years old ?   How many screw included ?   How many pieces included ?
    
    1                24                            22                        18                          14
    
    2                11                            31                        5                           16
    
    3                27                             19                       44                           17
     
    4                  2                            14                        12                          15



    Any help please ?

    Thanks

    Tuesday, November 17, 2015 6:44 PM

Answers

  • User-1716253493 posted

    Hii, modify your table like this

    ItemID QuestionCode Results
    1 q1 24
    1 q2 22
    1 q3 18
    1 q4 14
    2 q1 11
    2 q2 31
    2 q3 5
    2 q4 16
    3 q1 27
    3 q2 18
    3 q3 44
    3 q4 17
    4 q1 2
    4 q2 14
    4 q3 12
    4 q4 15

    To make query results like above from existing table you can do

    Select questionid, 'q1' as QuestionCode, q1 as reults from ItemQuestion
    union all
    Select questionid, 'q2' as QuestionCode, q2 as results from ItemQuestion
    union all
    Select questionid, 'q3' as QuestionCode, q3 as results from ItemQuestion
    union all
    Select questionid, 'q4' as QuestionCode, q4 as results from ItemQuestion
    

    the query results above is similar with new modified table above

    You can joint questions table with the new modified table or the query

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 7:12 PM
  • User1867929564 posted

    No doubt,that your table design is bad.

    you can try,

    declare @t table(QuestionCode varchar(10),Question varchar(100))
    insert into @t values('q1','How many gram can you hold ?')
    ,('q2','How many years old ?')
    ,('q3','How many screw included ?'),('q4','How many pieces included ?')
    
    declare @t1 table(ItemID int, q1 int,q2 int,q3 int, q4 int)
    insert into @t1 values
    (1,24  , 22, 18   , 14)
    ,(2, 11 , 31, 5    , 16)
    ,(3, 27 , 18, 44   , 17)
    ,(4, 2  , 14,  12  , 15)
    --select * from @t1
    							  
    SELECT 0 itemid
    	,[How many gram can you hold ?]
    	,[How many years old ?]
    	,[How many screw included ?]
    	,[How many pieces included ?]
    FROM (
    	SELECT *
    	FROM @t
    	) AS src
    pivot(count(QuestionCode) FOR Question IN (
    			[How many gram can you hold ?]
    			,[How many years old ?]
    			,[How many screw included ?]
    			,[How many pieces included ?]
    			)) AS pvt
    WHERE [How many gram can you hold ?] = - 1
    
    UNION ALL
    
    SELECT ItemID
    	,q1
    	,q2
    	,q3
    	,q4
    FROM @t1
    
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 11, 2015 10:39 AM

All replies

  • User-1716253493 posted

    Hii, modify your table like this

    ItemID QuestionCode Results
    1 q1 24
    1 q2 22
    1 q3 18
    1 q4 14
    2 q1 11
    2 q2 31
    2 q3 5
    2 q4 16
    3 q1 27
    3 q2 18
    3 q3 44
    3 q4 17
    4 q1 2
    4 q2 14
    4 q3 12
    4 q4 15

    To make query results like above from existing table you can do

    Select questionid, 'q1' as QuestionCode, q1 as reults from ItemQuestion
    union all
    Select questionid, 'q2' as QuestionCode, q2 as results from ItemQuestion
    union all
    Select questionid, 'q3' as QuestionCode, q3 as results from ItemQuestion
    union all
    Select questionid, 'q4' as QuestionCode, q4 as results from ItemQuestion
    

    the query results above is similar with new modified table above

    You can joint questions table with the new modified table or the query

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 7:12 PM
  • User1867929564 posted

    No doubt,that your table design is bad.

    you can try,

    declare @t table(QuestionCode varchar(10),Question varchar(100))
    insert into @t values('q1','How many gram can you hold ?')
    ,('q2','How many years old ?')
    ,('q3','How many screw included ?'),('q4','How many pieces included ?')
    
    declare @t1 table(ItemID int, q1 int,q2 int,q3 int, q4 int)
    insert into @t1 values
    (1,24  , 22, 18   , 14)
    ,(2, 11 , 31, 5    , 16)
    ,(3, 27 , 18, 44   , 17)
    ,(4, 2  , 14,  12  , 15)
    --select * from @t1
    							  
    SELECT 0 itemid
    	,[How many gram can you hold ?]
    	,[How many years old ?]
    	,[How many screw included ?]
    	,[How many pieces included ?]
    FROM (
    	SELECT *
    	FROM @t
    	) AS src
    pivot(count(QuestionCode) FOR Question IN (
    			[How many gram can you hold ?]
    			,[How many years old ?]
    			,[How many screw included ?]
    			,[How many pieces included ?]
    			)) AS pvt
    WHERE [How many gram can you hold ?] = - 1
    
    UNION ALL
    
    SELECT ItemID
    	,q1
    	,q2
    	,q3
    	,q4
    FROM @t1
    
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 11, 2015 10:39 AM
  • User77042963 posted

    Please post your question with DDL script and INSERTs for sample data. Thanks. 

    Friday, December 11, 2015 2:37 PM