Solved

SQL...Need to pivot data

Posted on 2013-01-16
12
364 Views
Last Modified: 2013-01-17
I have a VIEW that creates a pivot. I need to pivot the tables within the view so that the data can be formatted similar to the attachment.

View:
SELECT     ORG_ID, [ORGANIZATION_NAME], [GROUP], [DO_YOU_HAVE_CORE_PRIVILEGES], [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2]
FROM         (SELECT     '' AS ORG_ID, qfield, qtext
                       FROM          q_core_privileges) o PIVOT (max(qtext) FOR qfield IN ([ORGANIZATION_NAME], [GROUP], [DO_YOU_HAVE_CORE_PRIVILEGES], 
                      [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2])) p
UNION ALL
SELECT     *
FROM         core_privileges

Open in new window


Output generated by VIEW:

Output generated by view

Desired output format:
Desired output format
0
Comment
Question by:swaggrK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38785303
Hi,

Can you post sample input data?

Regards
  David
0
 

Author Comment

by:swaggrK
ID: 38785628
@dtodd, I hope this helps...

The input data is reflected in the image below.

For example, if you look at the field CORE_PRIVILEGES_A16_M1, in the attached image, it contains the following values:

Table 1 q_core_privileges
qid	qfield	                                                qtext
1	ORGANIZATION_NAME	                      Organization Name
2	CORE_PRIVILEGES_A16_M1	              Write admission orders-APRN
3	CORE_PRIVILEGES_A16_M2	              Write admission orders-PA

Open in new window

 


Table 2 core_privileges
ORG_ID	ORGANIZATION_NAME	CORE_PRIVILEGES_A16_M1 CORE_PRIVILEGES_A16_M2
1	        Northwestern Memorial Hospital		1	            1
2	        Rush University Medical Center		1	            1

Open in new window



View:
SELECT     ORG_ID, [ORGANIZATION_NAME],  [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2]
FROM         (SELECT     '' AS ORG_ID, qfield, qtext
                       FROM          q_core_privileges) o PIVOT (max(qtext) FOR qfield IN ([ORGANIZATION_NAME], [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2])) p
UNION ALL
SELECT     *
FROM         core_privileges

Open in new window


Output from VIEW:
Output from VIEW
Desired output:
 Desired output





Sample Input Data:
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38786011
Hi,

Thanks. That helps.

But to be sure to be sure to be sure, can you expand the inputs so there are 48 hospitals (or whatever) and the full list of privileges?

Thanks
  David
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:swaggrK
ID: 38787093
@dtodd --  i have uploaded an excel document with data

Excel document with data

Also, here is the actual VIEW of that data.
SELECT     ORG_ID, [ORGANIZATION_NAME], [GROUP], [DO_YOU_HAVE_CORE_PRIVILEGES], [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2], 
                      [CORE_PRIVILEGES_A17_M1], [CORE_PRIVILEGES_A17_M2], [CORE_PRIVILEGES_A18_M1], [CORE_PRIVILEGES_A18_M2], 
                      [CORE_PRIVILEGES_A4_M1], [CORE_PRIVILEGES_A4_M2], [CORE_PRIVILEGES_A5_M1], [CORE_PRIVILEGES_A5_M2], [CORE_PRIVILEGES_A6_M1], 
                      [CORE_PRIVILEGES_A6_M2], [CORE_PRIVILEGES_A7_M1], [CORE_PRIVILEGES_A7_M2], [CORE_PRIVILEGES_A9_M1], [CORE_PRIVILEGES_A9_M2], 
                      [CORE_PRIVILEGES_A10_M1], [CORE_PRIVILEGES_A10_M2], [CORE_PRIVILEGES_A8_M1], [CORE_PRIVILEGES_A8_M2], 
                      [CORE_PRIVILEGES_A11_M1], [CORE_PRIVILEGES_A11_M2], [CORE_PRIVILEGES_A13_M1], [CORE_PRIVILEGES_A13_M2], 
                      [CORE_PRIVILEGES_A14_M1], [CORE_PRIVILEGES_A14_M2], [CORE_PRIVILEGES_A2_M1], [CORE_PRIVILEGES_A2_M2], 
                      [CORE_PRIVILEGES_A19_M1], [CORE_PRIVILEGES_A19_M2]
FROM         (SELECT     '' AS ORG_ID, qfield, qtext
                       FROM          q_core_privileges) o PIVOT (max(qtext) FOR qfield IN ([ORGANIZATION_NAME], [GROUP], [DO_YOU_HAVE_CORE_PRIVILEGES], 
                      [CORE_PRIVILEGES_A16_M1], [CORE_PRIVILEGES_A16_M2], [CORE_PRIVILEGES_A17_M1], [CORE_PRIVILEGES_A17_M2], 
                      [CORE_PRIVILEGES_A18_M1], [CORE_PRIVILEGES_A18_M2], [CORE_PRIVILEGES_A4_M1], [CORE_PRIVILEGES_A4_M2], 
                      [CORE_PRIVILEGES_A5_M1], [CORE_PRIVILEGES_A5_M2], [CORE_PRIVILEGES_A6_M1], [CORE_PRIVILEGES_A6_M2], [CORE_PRIVILEGES_A7_M1], 
                      [CORE_PRIVILEGES_A7_M2], [CORE_PRIVILEGES_A9_M1], [CORE_PRIVILEGES_A9_M2], [CORE_PRIVILEGES_A10_M1], 
                      [CORE_PRIVILEGES_A10_M2], [CORE_PRIVILEGES_A8_M1], [CORE_PRIVILEGES_A8_M2], [CORE_PRIVILEGES_A11_M1], 
                      [CORE_PRIVILEGES_A11_M2], [CORE_PRIVILEGES_A13_M1], [CORE_PRIVILEGES_A13_M2], [CORE_PRIVILEGES_A14_M1], 
                      [CORE_PRIVILEGES_A14_M2], [CORE_PRIVILEGES_A2_M1], [CORE_PRIVILEGES_A2_M2], [CORE_PRIVILEGES_A19_M1], 
                      [CORE_PRIVILEGES_A19_M2])) p
UNION ALL
SELECT     *
FROM         core_privileges

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 38789063
Hi,

What is the final output in? What are you using for your presentation layer? Excel/Crystal/SSRS?

If the labels have to be data driven - ie from q_core_priviledes then you are asking for some fairly nasty looking dynamic code. Some of that labelling best left to the presentation layer if its fairly static. I mean, you have specific columns that link to specific privileges - changing them requires additional columns. And then you'll need to rework the previous rows to add in the additional data.

Regards
  David
0
 

Author Comment

by:swaggrK
ID: 38789103
The presentation layer would be Excel. I don't mind creating additional columns or even a table to accomplish the goal. Even if that meant doing a bulk insert into a different table structure. I am really not sure what is the best approach. This is all some zany survey data that I just have to make sense of...

I created q_core_privileges to simply hold the question text.

Regrads
0
 
LVL 35

Accepted Solution

by:
David Todd earned 400 total points
ID: 38789240
Hi,

declare @totalHospitals float

select @totalHospitals = count( * )
from dbo.core_privileges

select
	'APRN' as Who
	, sum( CORE_PRIVILEGES_A16_M1 ) as 'Write admission orders'
	, sum( CORE_PRIVILEGES_A17_M1 ) as 'Write discharge orders'
	, sum( CORE_PRIVILEGES_A18_M1 ) as 'Write transfer orders'
	, sum( CORE_PRIVILEGES_A4_M1 ) as 'Obtain history & physical'
	, sum( CORE_PRIVILEGES_A5_M1 ) as 'Order & interpret diagnostic testing and therapeutic modalities'
	, sum( CORE_PRIVILEGES_A6_M1 ) as 'Order & perform referrals and consults'
	, sum( CORE_PRIVILEGES_A7_M1 ) as 'Order blood & blood products'
	, sum( CORE_PRIVILEGES_A9_M1 ) as 'Order & manage conscious sedation'
	, sum( CORE_PRIVILEGES_A10_M1 ) as 'Order inpatient non-schedule medications'
	, sum( CORE_PRIVILEGES_A8_M1 ) as 'Order inpatient schedule (II-V) medications'
	, sum( CORE_PRIVILEGES_A11_M1 ) as 'Order topical anesthesia'
	, sum( CORE_PRIVILEGES_A13_M1 ) as 'Prescribes outpatient non-schedule medications'
	, sum( CORE_PRIVILEGES_A14_M1 ) as 'Prescribes outpatient schedule (II-V) medications'
	, sum( CORE_PRIVILEGES_A2_M1 ) as 'Incision & drainage with or without packing'
	, sum( CORE_PRIVILEGES_A19_M1 ) as 'Other'
from dbo.core_privileges  

union all

select
	'PA' as Who
	, sum( CORE_PRIVILEGES_A16_M2 ) as CORE_PRIVILEGES_A16_M2
	, sum( CORE_PRIVILEGES_A17_M2 ) as CORE_PRIVILEGES_A17_M2
	, sum( CORE_PRIVILEGES_A18_M2 ) as CORE_PRIVILEGES_A18_M2
	, sum( CORE_PRIVILEGES_A4_M2 ) as CORE_PRIVILEGES_A4_M2
	, sum( CORE_PRIVILEGES_A5_M2 ) as CORE_PRIVILEGES_A5_M2
	, sum( CORE_PRIVILEGES_A6_M2 ) as CORE_PRIVILEGES_A6_M2
	, sum( CORE_PRIVILEGES_A7_M2 ) as CORE_PRIVILEGES_A7_M2
	, sum( CORE_PRIVILEGES_A9_M2 ) as CORE_PRIVILEGES_A9_M2
	, sum( CORE_PRIVILEGES_A10_M2 ) as CORE_PRIVILEGES_A10_M2
	, sum( CORE_PRIVILEGES_A8_M2 ) as CORE_PRIVILEGES_A8_M2
	, sum( CORE_PRIVILEGES_A11_M2 ) as CORE_PRIVILEGES_A11_M2
	, sum( CORE_PRIVILEGES_A13_M2 ) as CORE_PRIVILEGES_A13_M2
	, sum( CORE_PRIVILEGES_A14_M2 ) as CORE_PRIVILEGES_A14_M2
	, sum( CORE_PRIVILEGES_A2_M2 ) as CORE_PRIVILEGES_A2_M2
	, sum( CORE_PRIVILEGES_A19_M2 ) as CORE_PRIVILEGES_A19_M2
from dbo.core_privileges  
;

select
	'APRN' as Who
	, sum( CORE_PRIVILEGES_A16_M1 ) / @totalHospitals as '% Write admission orders'
	, sum( CORE_PRIVILEGES_A17_M1 ) / @totalHospitals as 'Write discharge orders'
	, sum( CORE_PRIVILEGES_A18_M1 ) / @totalHospitals as 'Write transfer orders'
	, sum( CORE_PRIVILEGES_A4_M1 ) / @totalHospitals as 'Obtain history & physical'
	, sum( CORE_PRIVILEGES_A5_M1 ) / @totalHospitals as 'Order & interpret diagnostic testing and therapeutic modalities'
	, sum( CORE_PRIVILEGES_A6_M1 ) / @totalHospitals as 'Order & perform referrals and consults'
	, sum( CORE_PRIVILEGES_A7_M1 ) / @totalHospitals as 'Order blood & blood products'
	, sum( CORE_PRIVILEGES_A9_M1 ) / @totalHospitals as 'Order & manage conscious sedation'
	, sum( CORE_PRIVILEGES_A10_M1 ) / @totalHospitals as 'Order inpatient non-schedule medications'
	, sum( CORE_PRIVILEGES_A8_M1 ) / @totalHospitals as 'Order inpatient schedule (II-V) medications'
	, sum( CORE_PRIVILEGES_A11_M1 ) / @totalHospitals as 'Order topical anesthesia'
	, sum( CORE_PRIVILEGES_A13_M1 ) / @totalHospitals as 'Prescribes outpatient non-schedule medications'
	, sum( CORE_PRIVILEGES_A14_M1 ) / @totalHospitals as 'Prescribes outpatient schedule (II-V) medications'
	, sum( CORE_PRIVILEGES_A2_M1 ) / @totalHospitals as 'Incision & drainage with or without packing'
	, sum( CORE_PRIVILEGES_A19_M1 ) / @totalHospitals as 'Other'
from dbo.core_privileges  

union all

select
	'PA' as Who
	, sum( CORE_PRIVILEGES_A16_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A17_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A18_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A4_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A5_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A6_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A7_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A9_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A10_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A8_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A11_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A13_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A14_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A2_M2 )  / @totalHospitals
	, sum( CORE_PRIVILEGES_A19_M2 )  / @totalHospitals
from dbo.core_privileges  
;
go

Open in new window



Now paste those two results sets into excel on one sheet change the cell format on the percentages - looks like this:
Excel InitialNow copy the top result set, and go to sheet2 and instead of just clicking paste, look for the transpose rows-columns paste-special button.

Do the same with the second result set, but don't select the headers for this one.

Rearrange the columns and you get
Excel Final
For me the key is that the APRN and PA thing isn't a separate permission or privilege, but another dimension.

I think you are trying to do too much with SQL. It is a data repository, not an analysis tool.

HTH
  David
0
 

Author Comment

by:swaggrK
ID: 38789307
@ David, this helped alot! Thank you very much.

And thanks for the tidbit on not trying to do everything in SQL.
0
 

Author Comment

by:swaggrK
ID: 38789341
I've requested that this question be closed as follows:

Accepted answer: 0 points for swaggrK's comment #a38789307

for the following reason:

Provided an excellent solution and guidance.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38789319
Hi,

Did you mean to delete the question or accept my comment as the solution?

Regards
  David
0
 

Author Comment

by:swaggrK
ID: 38789339
No, that was a mistake.
0
 

Author Closing Comment

by:swaggrK
ID: 38789342
Excellent Expert!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL select to return records that don't exist (crosjoin?) 3 40
T-SQL: Only Wanting One Record 8 61
T-SQL Query 9 35
Datatable / Dates ? 4 30
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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