Solved

SQL...Need to pivot data

Posted on 2013-01-16
12
355 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 31
Get the latest status 8 32
Increment column based of a FK 8 23
performance query 4 24
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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