Solved

SQL...Need to pivot data

Posted on 2013-01-16
12
344 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
  • 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

11 Experts available now in Live!

Get 1:1 Help Now