How to Add A Second Inner Joined Table to SQL Server Pivot Query

Question

How to Add A Second Inner Joined Table to SQL Server Pivot Query

Al C 41

Good Evening,

I have been trying to add a second inner-joined table by the name of HS_SYS_ID_500A to the SQL Server query below. The join would be on health_sys_id.

SELECT * FROM
(
SELECT Value, health_sys_id,ffy, LineNumber = R.LINE_NUM_START+'-'+R.LINE_NUM_END
FROM Query1
INNER JOIN LINE_NUM_RANGE3 R ON LineNumber BETWEEN R.LINE_NUM_START AND LINE_NUM_END
WHERE (health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365') AND NOT (LineNumber ='862')

) t
PIVOT(
SUM(Value)
FOR ffy IN (
[2018],
[2019],
[2020],
[2021])
) AS pivot_table
ORDER BY health_sys_id, category

Thank you for taking the time to read and respond to this question.

Al

Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-01-06T03:05:59.447+00:00

You need to provide a mark up sample table along with sample data and expected result.
A pivot query can be written in different ways. With sample tables, it should be easy to come up with a query.
AlC 1 Reputation point

2023-01-06T13:33:08.623+00:00

Good Morning, Thank you for responding to my question. The following "mark-ups" for tables used in my query:
Query1
|health_sys_id| ffy | Attrbute | Value | Line_Number | LineNumber | health_sys_name |
|HSQ0000014| 2020 | INP | 100 | S300_05 | 138 | New Co |

LINE_NUM_RANGE3
|LINE_NUM_RANGE| LINE_NUM_START | LINE_NUM_END | CATEGORY |
| 389-393 | 389 | 393 | NRCC |

The above two tables (Query1, LINE_NUM_RANGE3) were used in the original query. I would like to join another table using an inner join. Here is a "mark-up" of the third table:
| health_sys_id | AMT |
| HSQ0000014 | 50 |

Thank you for taking the time to read and respond to this question. Looking forward to your comments.

Al

Accepted answer

1 additional answer

Your answer

Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-01-06T03:05:59.447+00:00

You need to provide a mark up sample table along with sample data and expected result.
A pivot query can be written in different ways. With sample tables, it should be easy to come up with a query.
AlC 1 Reputation point

2023-01-06T13:33:08.623+00:00

Good Morning, Thank you for responding to my question. The following "mark-ups" for tables used in my query:
Query1
|health_sys_id| ffy | Attrbute | Value | Line_Number | LineNumber | health_sys_name |
|HSQ0000014| 2020 | INP | 100 | S300_05 | 138 | New Co |

LINE_NUM_RANGE3
|LINE_NUM_RANGE| LINE_NUM_START | LINE_NUM_END | CATEGORY |
| 389-393 | 389 | 393 | NRCC |

The above two tables (Query1, LINE_NUM_RANGE3) were used in the original query. I would like to join another table using an inner join. Here is a "mark-up" of the third table:
| health_sys_id | AMT |
| HSQ0000014 | 50 |

Thank you for taking the time to read and respond to this question. Looking forward to your comments.

Al

Answer 1

Anonymous

Hi @Al C

This time I just added a third table using an inner join.

SELECT *   
FROM (SELECT A.Value,A.health_sys_id,A.ffy,LineNumber = R.LINE_NUM_START +'-'+ R.LINE_NUM_END  
      FROM Query1 as A  
      INNER JOIN LINE_NUM_RANGE3 R ON LineNumber BETWEEN R.LINE_NUM_START AND R.LINE_NUM_END  
      INNER JOIN HS_SYS_ID_500A as B on A.health_sys_id = B.health_sys_id  
      WHERE (A.health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365') AND NOT (LineNumber ='862')  
     ) t  
PIVOT(SUM(Value) FOR ffy IN ([2018],[2019],[2020],[2021])) AS pivot_table  
ORDER BY health_sys_id, category;

I tried based on the three tables of data you provided, but nothing can be output. Because I don't know what the result you need the final output to look like.

Best regards,
Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

AlC 1 Reputation point

2023-01-10T15:28:43.28+00:00

Good Morning,

After marking your response as the answer, I got a follow-up email from Q&A asking me to click on the verify answer button. I have been unable to find the verify answer button. I would very much like to follow-all the rules of this website.

Please advise.

Al
Anonymous

2023-01-11T09:09:15.8133333+00:00

Hi @AIC

You are so polite.

The general process on the forum is that the customer asks questions, and we communicate with the customer and propose solutions. If the customer's question is solved, the customer accepts the answer.

Sometimes a rating window for our service pops up, and customers rate according to the actual situation.

You have done what you should have done.

If you encounter any problems later, you can come to the forum for help. We will do our best to help you solve the problem.

Best regards,

Percy Tang

Answer 2

Anonymous

Hi @Al C

Based on your request, I tried to write the following query. Since there are some confusing places in your code, I made some modifications to these places. If you are still confused, you can continue to leave a message.

;With CTE as(  
  SELECT A.Value,A.health_sys_id,A.ffy,R.LINE_NUM_END - R.LINE_NUM_START as LineNumber  
  FROM Query1 as A  
  INNER JOIN LINE_NUM_RANGE3 as R ON R.LINE_NUM_END - R.LINE_NUM_START BETWEEN R.LINE_NUM_START AND R.LINE_NUM_END  
  INNER JOIN HS_SYS_ID_500A as B ON A.health_sys_id = B.health_sys_id)  
SELECT * FROM  
(select * from CTE WHERE health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365' AND LineNumber <> '862') as t  
PIVOT(SUM(Value) FOR ffy IN ([2018],[2019],[2020],[2021])) AS pivot_table  
ORDER BY health_sys_id, category

Best regards,
Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

AlC 1 Reputation point

2023-01-06T13:36:56.823+00:00

Good Morning

Thank you responding. I could not get your suggestion to run. It's my fault for not submitting "mock-up" tables to better illustrate my question.
Query1
|health_sys_id| ffy | Attrbute | Value | Line_Number | LineNumber | health_sys_name |
|HSQ0000014| 2020 | INP | 100 | S300_05 | 138 | New Co |

LINE_NUM_RANGE3
|LINE_NUM_RANGE| LINE_NUM_START | LINE_NUM_END | CATEGORY |
| 389-393 | 389 | 393 | NRCC |

The above two tables (Query1, LINE_NUM_RANGE3) were used in the original query. I would like to join another table using an inner join. Here is a "mark-up" of the third table:
| health_sys_id | AMT |
| HSQ0000014 | 50 |

Thank you for taking the time to read and respond to this question. Looking forward to your comments.
Jingyang Li 5,896 Reputation points Volunteer Moderator

2023-01-10T15:38:07.977+00:00

Thanks for trying to posting your mockup sample tables. Your question is a straight PIVOT question with a few table joins. Here is the better way to ask this question: Provide your sample table DDL scripts and some sample data and your expected result from the sample data. In your new postings, you missed to ask what you want to get from the sample data. If you include these requirements, you will get a test query much faster. Thanks.

Share via

How to Add A Second Inner Joined Table to SQL Server Pivot Query

1 additional answer

Your answer