Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

SQL...Need to pivot data

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
swaggrK
Asked:
swaggrK
  • 7
  • 5
1 Solution
 
David ToddSenior DBACommented:
Hi,

Can you post sample input data?

Regards
  David
0
 
swaggrKAuthor Commented:
@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
 
David ToddSenior DBACommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
swaggrKAuthor Commented:
@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
 
David ToddSenior DBACommented:
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
 
swaggrKAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
swaggrKAuthor Commented:
@ David, this helped alot! Thank you very much.

And thanks for the tidbit on not trying to do everything in SQL.
0
 
swaggrKAuthor Commented:
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
 
David ToddSenior DBACommented:
Hi,

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

Regards
  David
0
 
swaggrKAuthor Commented:
No, that was a mistake.
0
 
swaggrKAuthor Commented:
Excellent Expert!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now