Can some one help pivot the following table?
I need it to look like the following, but I'm struggling here.
| Measure | rate | target
| Kidney 1 Year Patient Survival | 98.36% | 97.46%
| Kidney 1 Year Graft Survival | 95.40% | 95.33%
| Liver 1 Year Patient Survival | 92.89% | 90.89%
| Liver 1 Year Graft Survival | 89.86% | 89.52%
Please post create table DDL and insert statement(s)
This is a straight raw file drop into a table from an excel spreadsheet via ssis.
CREATE TABLE [dbo].[lz_hb_spec_transplant_src_raw1](
[Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*] varchar NULL,
[Column 1] varchar NULL,
[Column 2] varchar NULL,
[Column 3] varchar NULL,
[Column 4] varchar NULL,
[Column 5] varchar NULL,
[Column 6] varchar NULL,
[Column 7] varchar NULL,
[Column 8] varchar NULL,
[Column 9] varchar NULL,
[Column 10] varchar NULL
) ON [PRIMARY]
USE [rdc_LZ]
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'"December 2016 SRTR Reporting Period (Actual Repor', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Metric', N'', N'"Kidney 1 Year
Patient Survival"', N'', N'"Kidney 1 Year
Graft Survival"', N'', N'', N'"Liver 1 Year
Patient Survival"', N'', N'"Liver 1 Year
Graft Survival"', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Calculated Actual', N'', N'97.47%', N'Not statistically signficantly lower than expected', N'94.67%', N'Not statistically signficantly lower than expected', N'', N'90.81%', N'Not statistically signficantly lower than expected', N'88.50%', N'Not statistically signficantly lower than expected')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Most Recent SRTR Expected Survival', N'', N'97.46%', N'', N'95.33%', N'', N'', N'90.89%', N'', N'89.52%', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'"Per SRTR Report (1 Year', N' 1 Sided P-Value)"', N'', N'0.54', N'', N'0.273', N'', N'', N'0.487', N'', N'0.319,')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'"June 2017 SRTR Reporting Period
(Projected Calcul', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Metric', N'', N'"Kidney 1 Year
Patient Survival"', N'', N'"Kidney 1 Year
Graft Survival"', N'', N'', N'"Liver 1 Year
Patient Survival"', N'', N'"Liver 1 Year
Graft Survival"', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Calculated Actual', N'', N'98.36%', N'Not anticipated to be statistically significantly ', N'95.40%', N'Not anticipated to be statistically significantly ', N'', N'92.89%', N'Not anticipated to be statistically significantly ', N'89.86%', N'Not anticipated to be statistically significantly ')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'Most Recent SRTR Expected Survival', N'', N'97.46%', N'', N'95.33%', N'', N'', N'90.89%', N'', N'89.52%', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
INSERT [dbo].[lz_hb_spec_transplant_src_raw1] ([Transplant Service Line Quality Metrics - Projected 1-Year Patient & Graft Survival*], [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10]) VALUES (N'*Based on data as of 01/03/2017', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'')
GO
The data you provided doesn't seem to lend itself to pivoting. Typically you define a row header column nd a column header column from the dataset and an aggregation such as sum, count.... Then the aggregate appears in the intersection of the row and column.
