question

Maarten-1921 avatar image
0 Votes"
Maarten-1921 asked EchoLiu-msft commented

T-Sql join

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For this type of question it is always a good idea to post CREATE TABLE statements for your tables and INSERT statements with the sample data, enough to illustrate all angles of the problem. We also need to see the expected result given the test data.

This makes it easy to copy and paste into a query window to develop a tested solution.

Note the emphasise above. You need to provide more questions to the RefTable, and explain how it maps to the DataTable. This table has a column A, but that appears to be a key in the RefTable?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Maarten-1921

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:
85466-image.png

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:
85489-image.png

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.






image.png (3.0 KiB)
image.png (2.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Maarten-1921 avatar image
0 Votes"
Maarten-1921 answered EchoLiu-msft commented
 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?


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, I am not very clear about your description.You seem to want to automatically complete the input of column names based on matching conditions instead of manual input. Please provide a complete minimum example for testing.

Echo

0 Votes 0 ·
Maarten-1921 avatar image
0 Votes"
Maarten-1921 answered Maarten-1921 commented

85794-image.png




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


image.png (36.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The ddl is sufficient for the example.

Yes, it is sufficient for the example you posted initially.

Then you start to talk about 150 column names, and I feel as lost as Echo when trying to understand what you mean by that. Sure, I can make guesses, but this is not a quiz show. If my guess is off the mark, it is frustrating for all of us.

0 Votes 0 ·

You are right there.
Maybe its better to post a dataset to clear the challenge. Its hard to get ddl from an existing table.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

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.

86093-image.png
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.



image.png (15.5 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can i send you an example of the data? Its a lot of work the make ddl for 150 columns. Although the data is anonymised i don't want to post the whole set here.

0 Votes 0 ·

Support you get is in public forum for free. The idea is that the question you ask, may help someone else in the future.

But that cannot happen if you take it to e-mail. I can't speak for Echo, but for my part, I would call that consulting and it would be billable. (Then again, the service level would be higher that you get for free here.)

However, neither I nor Echo are asking for all 150 columns. Nor do we ask for the real data. But we are asking for an extended examples with more columns, so we understand it better. It may be that I have not been paying attention, but I am not sure that know which one of the tables that has these 150 columns.

0 Votes 0 ·

What ErlandSommarskog said is exactly what I want to express. Microsoft provides paid consulting services, but as far as the forum is concerned, problems can only be solved through the forum.
Yes, we only need more extended columns, as long as it can explain the problem, three or four columns are fine, and 150 columns are not needed.

You may not be familiar enough with the current forum. When posting a new question of this type, what we need is: a minimal example, which includes your table structure (provide what is involved in the question), and some for testing Data, and the desired result of your example.

Always remember, what you need to provide is a small example, you don't need to provide complete data and huge table structure at all.

Regards
Echo






0 Votes 0 ·