Solved

SQL...Need to pivot data

Posted on 2013-01-16
12
373 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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