T-Sql join

Question

Hi,

I want to join 2 tables

Mark that the discription is in the ref table and also the possible answer values (as columns 1, 2 3).

How would you approach this?

DataTable

ID A

100 1

101 2

102 3

RefTable

QuestionID Discription 1 2 3

A How you feeling? Awesome Good Bad

Needed output

ID How you feeling?

100 Awesome

101 Good

102 Bad

Answer 1

Hi @Anonymous ，

Welcome to microsoft TSQL forum!

As ErlandSommarskog said, when you post a question, please post the relevant details.

Here are some tests based on my understanding, please refer to:

   CREATE TABLE #DataTable(ID INT,A INT)  
    INSERT INTO #DataTable VALUES(100,1),(101,2),(102,3)  
      
    CREATE TABLE #RefTable([QuestionID Discription] INT,[How you feeling?] CHAR(15))  
    INSERT INTO #RefTable VALUES(1,'Awesome'),(2,'Good'),(3,'Bad')  
      
    SELECT * FROM #DataTable  
    SELECT * FROM #RefTable  
      
    SELECT d.ID,r.[How you feeling?] FROM #DataTable d  
    INNER JOIN #RefTable r  
    ON d.A=r.[QuestionID Discription]  

Output：

Or:

CREATE TABLE #DataTable2(ID INT,A INT)  
INSERT INTO #DataTable2 VALUES(100,1),(101,2),(102,3)  
  
CREATE TABLE #RefTable2(QuestionID CHAR(25),Discription CHAR(25),[1] CHAR(25),[2] CHAR(25),[3] CHAR(25))  
INSERT INTO #RefTable2 VALUES('A','How you feeling?','Awesome','Good','Bad')  
  
  
SELECT * FROM #DataTable2  
SELECT * FROM #RefTable2  
  
;WITH cte  
as(SELECT * FROM #RefTable2   
UNPIVOT (val for num in ([1],[2],[3])) as t)  
SELECT ID,val AS [How you feeling?] FROM #DataTable2 d  
INNER JOIN cte c ON d.A=c.num  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

CREATE TABLE #DataTable2(ID INT,A INT)
 INSERT INTO #DataTable2 VALUES(100,1),(101,2),(102,3)

 CREATE TABLE #RefTable2(QuestionID CHAR(25),Discription CHAR(25),[1] CHAR(25),[2] CHAR(25),[3] CHAR(25))
 INSERT INTO #RefTable2 VALUES('A','How you feeling?','Awesome','Good','Bad')


 SELECT * FROM #DataTable2
 SELECT * FROM #RefTable2

 ;WITH cte
 as(SELECT * FROM #RefTable2 
 UNPIVOT (val for num in ([1],[2],[3])) as t)
 SELECT ID,val AS [How you feeling?] FROM #DataTable2 d
 INNER JOIN cte c ON d.A=c.num

This answers the question partially (thanks for that). The unpivot of the actual data solves the data problem.

The column names (reftable.discription) are in the ref table as well. I have over 150 column names so to get them out of the ref table would be nice instead of scripting them by hand (as in the script above is done AS [How you feeling?]).
So additionally to the unpivot of the data itself, can we match the question ID (datatable.A) in the data table, with the QuestionID (reftable.questionID) in the ref table and then show the discription (reftable.discription).

Is this possible?

Answer 3

The ddl is sufficient for the example. The first circle (datatable.a) needs to be matched with the second circle (reftable.questionID).

SELECT * FROM #RefTable2
where questionid = 'A'

reftable.discription is the column that we need to display, identical to the provided UNPIVOT script. In the UNPIVOT script bij EchoLiu-msft this is done manualy.
Maybe we need to chop it down into 2 datasets, one as suggested and one to match the questionID

Answer 4

What do you mean by 150 columns? Please add at least one more column as an example. In short, for your more column structure and its data storage method is currently unknown, please provide this information and your expected output.

If your data are all in one column, you don't actually need to complete all the conversions manually：

    CREATE TABLE #DataTable3(ID INT,A INT)  
    INSERT INTO #DataTable3 VALUES(100,1),(101,2),(102,3)  
              
    CREATE TABLE #RefTable3(QuestionID CHAR(55),Discription CHAR(55),[1] CHAR(25),[2] CHAR(25),[3] CHAR(25))  
    INSERT INTO #RefTable3 VALUES('A','How you feeling?','Awesome','Good','Bad')  
                                   ,('A','Do you like to read books?','Like very much','Like','dislike')  
              
              
    SELECT * FROM #DataTable3  
    SELECT * FROM #RefTable3  
              
    ;WITH cte  
    as(SELECT * FROM #RefTable3   
    UNPIVOT (val for num in ([1],[2],[3])) as t)  
    SELECT ID,val AS [How you feeling?] FROM #DataTable3 d  
    INNER JOIN cte c ON d.A=c.num  

When you talk about each issue, please remember to provide us with the table structure(CREARE statement) and test data(INSERT INTO statement) involved, otherwise it will be difficult for us to understand your needs.