January 27, 2015 at 11:59 pm
Good day,
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not i desire,
here is my code
@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #x
(
_ATOR int,
_BfirstDraftProposal int,
_CreviseProposal int,
_DrevisedProposal2 int,
_EreviewOfPMProposal int,
_FreviewByTRPMember1 int,
_GreviewByTRPMember2 int,
_HreviewByTRPMember3 int,
_IconsolOfComments int,
_JtrCommentSentProposal int,
_KreceiptOfRevisedProp3 int,
_LproposalSentToTRPMember3 int,
_MpropsalFinalized4 int,
_NeRCCommentPRop3 int,
_OrevPropBasedOnERCCommentsRevProp5 int,
_PmOADraft int,
_QmOASigning int,
_RfnlDataCollectionTools int,
_StypeDataColInFirstSiteProgressReport int,
_TdataCollectionProgressReport int,
_UdataCollectionDataProcess int,
_VdataProcessiongComplete int,
_WpreliminaryResultReport int,
_XreviewOfPMPreliminaryReport int,
_YreceiptOfTheFirstDraftTerminalReportDraft int,
_ZreviewOfPmTerminalReportDraft1 int,
AreviewOFTRPTerminalReportDraft1 int,
BreviewOfTRPMember2TerminalReportDraft1 int,
CreviewOfTheMember3TerminalReportDraft1 int,
DprogressReportFQuarter int,
EprogressReportSndQuarter int,
FprogressReportThrdQuarter int,
GfieldVisitProgressReport int,
HfrstVoucher int,
IsecondVoucher int,
JthirdVoucher int,
KfQFinancialReport int,
LsQFinancialReport int,
MtQFinancialReport int,
NfinalFRWithAccountsPayable int,
Obriefer int,
PhPNotes int,
QjMScript int,
dt int
)
INSERT INTO #x
(
_ATOR,
_BfirstDraftProposal,
_CreviseProposal,
_DrevisedProposal2,
_EreviewOfPMProposal,
_FreviewByTRPMember1,
_GreviewByTRPMember2,
_HreviewByTRPMember3,
_IconsolOfComments,
_JtrCommentSentProposal,
_KreceiptOfRevisedProp3,
_LproposalSentToTRPMember3,
_MpropsalFinalized4,
_NeRCCommentPRop3,
_OrevPropBasedOnERCCommentsRevProp5,
_PmOADraft,
_QmOASigning,
_RfnlDataCollectionTools,
_StypeDataColInFirstSiteProgressReport,
_TdataCollectionProgressReport,
_UdataCollectionDataProcess,
_VdataProcessiongComplete,
_WpreliminaryResultReport,
_XreviewOfPMPreliminaryReport,
_YreceiptOfTheFirstDraftTerminalReportDraft,
_ZreviewOfPmTerminalReportDraft1,
AreviewOFTRPTerminalReportDraft1,
BreviewOfTRPMember2TerminalReportDraft1,
CreviewOfTheMember3TerminalReportDraft1,
DprogressReportFQuarter,
EprogressReportSndQuarter,
FprogressReportThrdQuarter,
GfieldVisitProgressReport,
HfrstVoucher,
IsecondVoucher,
JthirdVoucher,
KfQFinancialReport,
LsQFinancialReport,
MtQFinancialReport,
NfinalFRWithAccountsPayable,
Obriefer,
PhPNotes,
QjMScript,
dt
)
select
case when b.torName is null then '0' else '1' end TOR,
case when b.firstDraftProposalName is null then '0' else '1' end firstDraftProposal,
case when b.reviseProposalName is null then '0' else '1' end reviseProposal,
case when b.revisedProposalName2 is null then '0' else '1' end revisedProposal2,
case when b.reviewOfPMProposalName is null then '0' else '1' end reviewOfPMProposal,
case when b.reviewByTRPMember1Name is null then '0' else '1' end reviewByTRPMember1,
case when b.reviewByTRPMember2Name is null then '0' else '1' end reviewByTRPMember2,
case when b.reviewByTRPMember3Name is null then '0' else '1' end reviewByTRPMember3,
case when b.consolOfCommentsName is null then '0' else '1' end consolOfComments,
case when b.trCommentSentProposalName is null then '0' else '1' end trCommentSentProposal,
case when b.receiptOfRevisedProp3Name is null then '0' else '1' end receiptOfRevisedProp3,
case when b.proposalSentToTRPMember3Name is null then '0' else '1' end proposalSentToTRPMember3,
case when b.propsalFinalized4Name is null then '0' else '1' end propsalFinalized4,
case when b.eRCCommentPRop3Name is null then '0' else '1' end eRCCommentPRop3,
case when b.revPropBasedOnERCCommentsRevProp5Name is null then '0' else '1' end revPropBasedOnERCCommentsRevProp5,
case when b.mOADraftName is null then '0' else '1' end mOADraft,
case when b.mOASigningName is null then '0' else '1' end mOASigning,
case when b.fnlDataCollectionToolsName is null then '0' else '1' end fnlDataCollectionTools,
case when B.typeDataColInFirstSiteProgressReportName is null then '0' else '1' end typeDataColInFirstSiteProgressReport,
case when B.dataCollectionProgressReportName is null then '0' else '1' end dataCollectionProgressReport,
case when B.dataCollectionDataProcessName is null then '0' else '1' end dataCollectionDataProcess,
case when b.dataProcessiongCompleteName is null then '0' else '1' end dataProcessiongComplete,
case when B.preliminaryResultReportName is null then '0' else '1' end preliminaryResultReport,
case when B.reviewOfPMPreliminaryReportName is null then '0' else '1' end reviewOfPMPreliminaryReport,
case when B.receiptOfTheFirstDraftTerminalReportDraftName is null then '0' else '1' end receiptOfTheFirstDraftTerminalReportDraft,
case when B.reviewOfPmTerminalReportDraft1Name is null then '0' else '1' end reviewOfPmTerminalReportDraft1,
case when B.reviewOFTRPTerminalReportDraft1Name is null then '0' else '1' end reviewOFTRPTerminalReportDraft1,
case when b.reviewOfTRPMember2TerminalReportDraft1Name is null then '0' else '1' end reviewOfTRPMember2TerminalReportDraft1,
case when B.reviewOfTheMember3TerminalReportDraft1Name is null then '0' else '1' end reviewOfTheMember3TerminalReportDraft1,
case when b.progressReportFQuarterName is null then '0' else '1' end progressReportFQuarter,
case when b.progressReportSndQuarterName is null then '0' else '1' end progressReportSndQuarter,
case when b.progressReportThrdQuarterName is null then '0' else '1' end progressReportThrdQuarter,
case when b.fieldVisitProgressReportName is null then '0' else '1' end fieldVisitProgressReport,
case when b.frstVoucherName is null then '0' else '1' end frstVoucher,
case when B.secondVoucherName is null then '0' else '1' end secondVoucher,
case when b.thirdVoucherName is null then '0' else '1' end thirdVoucher,
case when b.fQFinancialReportName is null then '0' else '1' end fQFinancialReport,
case when B.sQFinancialReportName is null then '0' else '1' end sQFinancialReport,
case when b.tQFinancialReportName is null then '0' else '1' end tQFinancialReport,
case when b.finalFRWithAccountsPayableName is null then '0' else '1' end finalFRWithAccountsPayable,
case when b.brieferName is null then '0' else '1' end briefer,
case when b.hPNotesName is null then '0' else '1' end hPNotes,
case when b.jMScriptName is null then '0' else '1' end jMScript,
year(proj_started) as dt
from
[dbo].[tbldocs] b
where year(proj_started) = @syear
declare @mydt float
SET @mydt = (select [noProject] as c from [dbo].[tblYear] where [syear]=@syear)
create table #y
(
Research nvarchar(MAX),
value float
)
insert into #y
(
Research,
value
)
-- SELECT REPLACE('_ATOR','_ATOR','Terms Of Referrence') FROM #x
select PRop,dat
from(select
_ATOR,
_BfirstDraftProposal,
_CreviseProposal,
_DrevisedProposal2,
_EreviewOfPMProposal,
_FreviewByTRPMember1,
_GreviewByTRPMember2,
_HreviewByTRPMember3,
_IconsolOfComments,
_JtrCommentSentProposal,
_KreceiptOfRevisedProp3,
_LproposalSentToTRPMember3,
_MpropsalFinalized4,
_NeRCCommentPRop3,
_OrevPropBasedOnERCCommentsRevProp5,
_PmOADraft,
_QmOASigning,
_RfnlDataCollectionTools,
_StypeDataColInFirstSiteProgressReport,
_TdataCollectionProgressReport,
_UdataCollectionDataProcess,
_VdataProcessiongComplete,
_WpreliminaryResultReport,
_XreviewOfPMPreliminaryReport,
_YreceiptOfTheFirstDraftTerminalReportDraft,
_ZreviewOfPmTerminalReportDraft1,
AreviewOFTRPTerminalReportDraft1,
BreviewOfTRPMember2TerminalReportDraft1,
CreviewOfTheMember3TerminalReportDraft1,
DprogressReportFQuarter,
EprogressReportSndQuarter,
FprogressReportThrdQuarter,
GfieldVisitProgressReport,
HfrstVoucher,
IsecondVoucher,
JthirdVoucher,
KfQFinancialReport,
LsQFinancialReport,
MtQFinancialReport,
NfinalFRWithAccountsPayable,
Obriefer,
PhPNotes,
QjMScript
from #x) mys
UNPIVOT (dat for PRop in(
_ATOR,
_BfirstDraftProposal,
_CreviseProposal,
_DrevisedProposal2,
_EreviewOfPMProposal,
_FreviewByTRPMember1,
_GreviewByTRPMember2,
_HreviewByTRPMember3,
_IconsolOfComments,
_JtrCommentSentProposal,
_KreceiptOfRevisedProp3,
_LproposalSentToTRPMember3,
_MpropsalFinalized4,
_NeRCCommentPRop3,
_OrevPropBasedOnERCCommentsRevProp5,
_PmOADraft,
_QmOASigning,
_RfnlDataCollectionTools,
_StypeDataColInFirstSiteProgressReport,
_TdataCollectionProgressReport,
_UdataCollectionDataProcess,
_VdataProcessiongComplete,
_WpreliminaryResultReport,
_XreviewOfPMPreliminaryReport,
_YreceiptOfTheFirstDraftTerminalReportDraft,
_ZreviewOfPmTerminalReportDraft1,
AreviewOFTRPTerminalReportDraft1,
BreviewOfTRPMember2TerminalReportDraft1,
CreviewOfTheMember3TerminalReportDraft1,
DprogressReportFQuarter,
EprogressReportSndQuarter,
FprogressReportThrdQuarter,
GfieldVisitProgressReport,
HfrstVoucher,
IsecondVoucher,
JthirdVoucher,
KfQFinancialReport,
LsQFinancialReport,
MtQFinancialReport,
NfinalFRWithAccountsPayable,
Obriefer,
PhPNotes,
QjMScript
)) as x
--- here i just update each row of the unpivot result just to show the correct result of the rows
----------------------------------------------------------
update #y
set
Research = '_A Terms Of Referrence'
where Research='_ATOR'
update #y
set
Research = '_B First Draft Proposal'
where Research='_BfirstDraftProposal'
update #y
set
Research = '_C Revise Proposal'
where Research='_CreviseProposal'
update #y
set
Research = '_D Revised Proposal 2'
where Research='_DrevisedProposal2'
update #y
set
Research = '_E Review Of PM Proposal'
where Research='_EreviewOfPMProposal'
update #y
set
Research = '_F Review By TRP Member 1'
where Research='_FreviewByTRPMember1'
update #y
set
Research = '_G Review By TRP Member 2'
where Research='_GreviewByTRPMember2'
update #y
set
Research = '_H Review By TRP Member 3'
where Research='_HreviewByTRPMember3'
update #y
set
Research = '_D Revised Proposal 2'
where Research='_DrevisedProposal2'
update #y
set
Research = '_I Consol Of Comments'
where Research='_IconsolOfComments'
update #y
set
Research = '_J Comment Sent Proposal'
where Research='_JtrCommentSentProposal'
update #y
set
Research = '_K Receipt Of RevisedProposal 3'
where Research='_KreceiptOfRevisedProp3'
update #y
set
Research = '_L Proposal Sent To TRP Member 3'
where Research='_LproposalSentToTRPMember3'
update #y
set
Research = '_M Propsal Finalized 4'
where Research='_MpropsalFinalized4'
update #y
set
Research = '_N ERC Comment Proposal 3'
where Research='_NeRCCommentPRop3'
update #y
set
Research = '_O Revise Proposal Based On ERC Comments'
where Research='_OrevPropBasedOnERCCommentsRevProp5'
update #y
set
Research = '_P MOA Draft'
where Research='_PmOADraft'
update #y
set
Research = '_Q MOA Signing'
where Research='_QmOASigning'
update #y
set
Research = '_R Final Data Collection Tools'
where Research='_RfnlDataCollectionTools'
update #y
set
Research = '_S Data Collection In First Site Progress Report'
where Research='_StypeDataColInFirstSiteProgressReport'
update #y
set
Research = '_T Data Collection Progress Report'
where Research='_TdataCollectionProgressReport'
update #y
set
Research = '_U Data Collection Data Process'
where Research='_UdataCollectionDataProcess'
update #y
set
Research = '_V Data Processing Complete'
where Research='_VdataProcessiongComplete'
update #y
set
Research = '_W Preliminary Result Report'
where Research='_WpreliminaryResultReport'
update #y
set
Research = '_X Review Of PM Preliminary Report'
where Research='_XreviewOfPMPreliminaryReport'
update #y
set
Research = '_Y Receipt Of The First Draft Terminal Report Draft'
where Research='_YreceiptOfTheFirstDraftTerminalReportDraft'
update #y
set
Research = '_Z Review Of PM Terminal Report Draft 1'
where Research='_ZreviewOfPmTerminalReportDraft1'
update #y
set
Research = 'A Review Of TRP Terminal Report Draft 1'
where Research='AreviewOFTRPTerminalReportDraft1'
update #y
set
Research = 'B Review Of TRP Member2 Terminal Report Draft 1'
where Research='BreviewOfTRPMember2TerminalReportDraft1'
update #y
set
Research = 'C Review Of The Member3 Terminal Report Draft 1'
where Research='CreviewOfTheMember3TerminalReportDraft1'
update #y
set
Research = 'D Progress Report First Quarter'
where Research='DprogressReportFQuarter'
update #y
set
Research = 'E Progress Report Second Quarter'
where Research='EprogressReportSndQuarter'
update #y
set
Research = 'F Progress Report Third Quarter'
where Research='FprogressReportThrdQuarter'
update #y
set
Research = 'G Field Visit Progress Report'
where Research='GfieldVisitProgressReport'
update #y
set
Research = 'H First Voucher'
where Research='HfrstVoucher'
update #y
set
Research = 'I Second Voucher'
where Research='IsecondVoucher'
update #y
set
Research = 'J Third Voucher'
where Research='JthirdVoucher'
update #y
set
Research = 'K First Quarter Financial Report'
where Research='KfQFinancialReport'
update #y
set
Research = 'L Second Quarter Financial Report'
where Research='LsQFinancialReport'
update #y
set
Research = 'M Third Quarter Financial Report'
where Research='MtQFinancialReport'
update #y
set
Research = 'N Final FR With Accounts Payable'
where Research='NfinalFRWithAccountsPayable'
update #y
set
Research = 'O Briefer'
where Research='Obriefer'
update #y
set
Research = 'P Health Policy Notes'
where Research='PhPNotes'
update #y
set
Research = 'Q Journal ManuScript'
where Research='QjMScript'
----------------------------------------------------------
select Research,sum(value/@mydt*100) as value from #y
group by value,Research
drop table #x
drop table #y
i hope someone can guide into the right direction
January 28, 2015 at 2:33 am
cyberdaemon (1/27/2015)
Good day,I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not i desire,
... loads of stuff here
i hope someone can guide into the right direction
You've posted in the SS2K8 section but state that you are using sql 2012; which is it?
I'd perform your aggregate/unpivot in a different way, like this:
-- Make some sample data
DROP TABLE #tblDocs
CREATE TABLE #tblDocs (
proj_started DATETIME,
torName VARCHAR(10),
firstDraftProposalName VARCHAR(10),
reviseProposalName VARCHAR(10),
revisedProposalName2 VARCHAR(10),
reviewOfPMProposalName VARCHAR(10),
reviewByTRPMember1Name VARCHAR(10),
reviewByTRPMember2Name VARCHAR(10),
reviewByTRPMember3Name VARCHAR(10))
INSERT INTO #tblDocs (proj_started, torName, firstDraftProposalName, reviseProposalName, revisedProposalName2,
reviewOfPMProposalName, reviewByTRPMember1Name, reviewByTRPMember2Name, reviewByTRPMember3Name)
SELECT '20150128', 'tn1', 'fdpn1', 'rpn1', 'rpn2_1', 'rppn1', 'trp1_1', 'trp2_1', 'trp3_1' UNION ALL
SELECT '20150128', 'tn2', NULL, 'rpn2', 'rpn2_2', NULL, 'trp1_2', NULL, NULL UNION ALL
SELECT '20150128', 'tn3', 'fdpn3', 'rpn3', NULL, 'rppn3', 'trp1_3', NULL, NULL UNION ALL
SELECT '20150128', 'tn4', 'fdpn4', NULL, 'rpn2_4', NULL, 'trp1_4', 'trp2_4', NULL UNION ALL
SELECT '20150128', 'tn5', 'fdpn5', 'rpn5', NULL, 'rppn5', 'trp1_5', NULL, NULL UNION ALL
SELECT '20150128', 'tn6', 'fdpn6', 'rpn6', 'rpn2_6', NULL, 'trp1_6', 'trp2_6', NULL UNION ALL
SELECT '20150128', 'tn7', 'fdpn7', NULL, NULL, 'rppn7', 'trp1_7', 'trp2_7', 'trp3_7' UNION ALL
SELECT '20150128', 'tn8', NULL, 'rpn8', NULL, NULL, 'trp1_8', NULL, NULL UNION ALL
SELECT '20150128', 'tn9', 'fdpn9', NULL, 'rpn2_9', 'rppn9', 'trp1_9', 'trp2_9', NULL
-------------------------------------------------------------------------
-- Aggregate the data using COUNT(columnname), which will eliminate nulls
-------------------------------------------------------------------------
DROP TABLE #Temp
SELECT
proj_started,
torName = COUNT(torName),
firstDraftProposalName = COUNT(firstDraftProposalName),
reviseProposalName = COUNT(reviseProposalName),
revisedProposalName2 = COUNT(revisedProposalName2),
reviewOfPMProposalName = COUNT(reviewOfPMProposalName),
reviewByTRPMember1Name = COUNT(reviewByTRPMember1Name),
reviewByTRPMember2Name = COUNT(reviewByTRPMember2Name),
reviewByTRPMember3Name = COUNT(reviewByTRPMember3Name)
INTO #Temp
FROM #tblDocs d
GROUP BY proj_started
-- Visual check (replace #temp with a CTE when you are familiar with the code)
SELECT * FROM #Temp
-------------------------------------------------------------------------
-- Manual unpivot, add a sequence column
-- Use CROSS APPLY VALUES if your server is 2012
-------------------------------------------------------------------------
SELECT proj_started, x.*
FROM #Temp t
CROSS APPLY (
SELECT 8, 'torName', torName UNION ALL
SELECT 2, 'firstDraftProposalName', firstDraftProposalName UNION ALL
SELECT 3, 'reviseProposalName', reviseProposalName UNION ALL
SELECT 4, 'revisedProposalName2', revisedProposalName2 UNION ALL
SELECT 5, 'reviewOfPMProposalName', reviewOfPMProposalName UNION ALL
SELECT 6, 'reviewByTRPMember1Name', reviewByTRPMember1Name UNION ALL
SELECT 7, 'reviewByTRPMember2Name', reviewByTRPMember2Name UNION ALL
SELECT 1, 'reviewByTRPMember3Name', reviewByTRPMember3Name
) x (Seq, Research, Value)
ORDER BY x.Seq
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply