Solved

Join two tables and use PIVOT function

Posted on 2012-04-10
6
248 Views
Last Modified: 2012-04-30
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
0
Comment
Question by:jvoconnell
  • 3
  • 3
6 Comments
 
LVL 14

Expert Comment

by:nishant joshi
Comment Utility
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
 
LVL 1

Author Comment

by:jvoconnell
Comment Utility
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
 
LVL 14

Expert Comment

by:nishant joshi
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 14

Expert Comment

by:nishant joshi
Comment Utility
sry for above solution
might other table having multiple records match so that it will goving more output...
0
 
LVL 1

Accepted Solution

by:
jvoconnell earned 0 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:jvoconnell
Comment Utility
Used multiple queries instead of PIVOT feature
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now