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
LVL 1
jvoconnellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nishant joshiTechnology Development ConsultantCommented:
SELECT PATIENTACCOUNT, FACILITYID, [1] AS PRINCPL_DX, [2] AS SEC_DX1, [3] AS SEC_DX2, [4] AS SEC_DX3, [5] AS SEC_DX4,[6] as SEC_DX5
      FROM
            (
            SELECT PATIENTACCOUNT, FACILITYID, ICD9DXSEQUENCE, ICD9DXCODE,b.description
            FROM T_ENCOUNTER_ICD9DX a join T_ICD9DX_Codes b
             ON a.ICD9DXCODE = b.ICD9DXCODE 
            ) DXCODES 
      PIVOT
            (
            MAX(ICD9DXCODE)
            FOR ICD9DXSEQUENCE IN ([1],[2],[3],[4],[5],[6])
            ) AS PVT 

Open in new window


regards,
0
jvoconnellAuthor Commented:
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.
0
nishant joshiTechnology Development ConsultantCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nishant joshiTechnology Development ConsultantCommented:
sry for above solution
might other table having multiple records match so that it will goving more output...
0
jvoconnellAuthor Commented:
I wanted to close the loop on this question. From the suggestions, I couldn't get the desired output I wanted. We ended up using multiple queries instead of the PIVOT feature due to time constraints.  I do want to thank you for your time and assistance
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvoconnellAuthor Commented:
Used multiple queries instead of PIVOT feature
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.