jvoconnell
asked on
Join two tables and use PIVOT function
Experts,
This is the first time I've used the PIVOT function in SQL Server. The query below gives me the desired result.
SELECT PATIENTACCOUNT, FACILITYID, [1] AS PRINCPL_DX, [2] AS SEC_DX1, [3] AS SEC_DX2, [4] AS SEC_DX3, [5] AS SEC_DX4
FROM
(
SELECT PATIENTACCOUNT, FACILITYID, ICD9DXSEQUENCE, ICD9DXCODE
FROM T_ENCOUNTER_ICD9DX
) DXCODES
PIVOT
(
MAX(ICD9DXCODE)
FOR ICD9DXSEQUENCE IN ([1],[2],[3],[4],[5])
) AS PVT
I'm wondering if it's possible to join to another table to achieve the result in this example below.
I've been searching a while now and can't seem to find anything that gets me to where I want to go.
I want to join the T_ENCOUNTER_ICD9DX table to a T_ICD9DX_Codes table ON
ICD9DXCODE column to get the Description of the Codes.
My desired result would be display the columns like ...
PATIENTACCOUNT, FACILITYID, [1] AS PRINCPL_DX, Princ_dx_description, [2] AS SEC_DX1, Sec_dx1_description,...
I've attached a simple create table / insert values script.
Any help is appreciated.
pivot-setup-table-script.sql
This is the first time I've used the PIVOT function in SQL Server. The query below gives me the desired result.
SELECT PATIENTACCOUNT, FACILITYID, [1] AS PRINCPL_DX, [2] AS SEC_DX1, [3] AS SEC_DX2, [4] AS SEC_DX3, [5] AS SEC_DX4
FROM
(
SELECT PATIENTACCOUNT, FACILITYID, ICD9DXSEQUENCE, ICD9DXCODE
FROM T_ENCOUNTER_ICD9DX
) DXCODES
PIVOT
(
MAX(ICD9DXCODE)
FOR ICD9DXSEQUENCE IN ([1],[2],[3],[4],[5])
) AS PVT
I'm wondering if it's possible to join to another table to achieve the result in this example below.
I've been searching a while now and can't seem to find anything that gets me to where I want to go.
I want to join the T_ENCOUNTER_ICD9DX table to a T_ICD9DX_Codes table ON
ICD9DXCODE column to get the Description of the Codes.
My desired result would be display the columns like ...
PATIENTACCOUNT, FACILITYID, [1] AS PRINCPL_DX, Princ_dx_description, [2] AS SEC_DX1, Sec_dx1_description,...
I've attached a simple create table / insert values script.
Any help is appreciated.
pivot-setup-table-script.sql
ASKER
Thank you for the input. This option results in the query returning multiple rows where as the original query returned one. I was hoping it was possible to keep the one row and have the descriptions be on the same line. Sorry about that, I don't think I was clear. I apologize. For now, I'll just populate a table with the results from the query and then do a second pass and go back and get the descriptions.
example of cte
http://msdn.microsoft.com/en-us/library/ms186243.aspx
There are too many examples you will find after googling...but i am giving simple one as below.
;with testcte(ID)
as
(select 1
union all
select ID+1 from testcte where ID < 5
)
select * from testcte
as above example will give a result of 1 to 4 and stop for 5.
thanks.
http://msdn.microsoft.com/en-us/library/ms186243.aspx
There are too many examples you will find after googling...but i am giving simple one as below.
;with testcte(ID)
as
(select 1
union all
select ID+1 from testcte where ID < 5
)
select * from testcte
as above example will give a result of 1 to 4 and stop for 5.
thanks.
sry for above solution
might other table having multiple records match so that it will goving more output...
might other table having multiple records match so that it will goving more output...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Used multiple queries instead of PIVOT feature
Open in new window
regards,