How do I put this proc into a pivot table?

Question

Post reply

How do I put this proc into a pivot table?

Dyan Sharp

SSC Rookie

Points: 27
More actions
April 28, 2005 at 7:34 am

#85971

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.usp_M10_63A2_WeeklyInstalledOrderReportDatabyDYAN
AS
--This proc was altered on 04/27/2005 by Dyan Sharp to put into pivot table output.
SELECT DISTINCT MarketSegmentDescriptions.[Description] AS Market,
       (CASE
  WHEN NewSales = 'yes' THEN 'New Sale'
  ELSE
   CASE
    WHEN Moves = 'yes' THEN 'Move'
    ELSE
     CASE
      WHEN Upgrades = 'yes' THEN 'Upgrade'
      ELSE
       CASE
        WHEN Downgrades = 'yes' THEN 'Downgrade'
        ELSE 'Admin'
       END
     END
   END
END) AS OrderType,
(CASE
  WHEN Type = 'b' THEN 'Business'
  ELSE 'Residential'
END) AS AccountType,
(CASE
  WHEN ((ReportingTable.Market = 'ISP') OR (ReportingTable.Market = 'CLEC')) THEN '9999999999'
  ELSE ReportingTable.RTN
END) AS PhoneNumber,
ReportingTable.OrderNumber,
ReportingTable.AppDate,
(CASE
  WHEN ((ReportingTable.Market = 'ISP') OR (ReportingTable.Market = 'CLEC')) THEN '9999999999999'
  ELSE ReportingTable.Account
END) AS AccountNumber,
ReportingTable.SAECode,
ReportingTable.PCB,
Location.Exchange,
Location.WireCenter,
Location.State,
Location.Region,
Location.MarketGroup,
Location.MarketGroup2P,
Location.CompetitiveMarket,
Location.DSLMarketName,
Location.SLCSSubGeographicMarket,
ReportingTable.Des4 AS EID,
ReportingTable.SI AS SelfInstall,
(CASE
  WHEN EID.Location = 'fane' THEN 'Outbound'
  ELSE
   CASE
    WHEN Channel.Chan IS NULL THEN 'Unknown'
    ELSE Channel.Channel
   END
END) AS Channel,
(CASE
  WHEN EID.EID IS NULL THEN 'Unknown'
  ELSE EID.Location
END) AS Location,
ReportingTable.OrderDate,
ReportingTable.DueDate,
ReportingTable.InstallDate,
Location.DMAName as DMAMarket,
Location.CLASWireCenter,
Speed.SpeedDescription,
Location.SBSMarketGroup,
OriginalInstallDates.AppDate AS OriginalAppDate,
OriginalInstallDates.InstallDate AS OriginalInstallDate,
CustBill.BillingName,
CustBill.BillingAddress1,
CustBill.BillingAddress2,
CustBill.BillingAddress3,
CustBill.BillingAddress4
FROM (((ReportingTable LEFT JOIN MarketSegmentDescriptions ON ReportingTable.Market = MarketSegmentDescriptions.Market)
LEFT JOIN Location ON ReportingTable.NPANXX = Location.NPANXX)
LEFT JOIN EID ON ReportingTable.Des4 = EID.EID)
LEFT JOIN Channel ON EID.Channel = Channel.Chan
LEFT JOIN Speed ON ReportingTable.Speed = Speed.Speed
LEFT JOIN OriginalInstallDates ON ((ReportingTable.RTN = OriginalInstallDates.RTN) AND (ReportingTable.Product = OriginalInstallDates.Product))
LEFT JOIN CustBill ON ReportingTable.Account = CustBill.Telephone
WHERE (((CASE
  WHEN NewSales = 'yes' THEN 'New Sale'
  ELSE
   CASE
    WHEN Moves = 'yes' THEN 'Move'
    ELSE
     CASE
      WHEN Upgrades = 'yes' THEN 'Upgrade'
      ELSE
       CASE
        WHEN Downgrades = 'yes' THEN 'Downgrade'
        ELSE 'Admin'
       END
     END
   END
END) <> 'Admin') AND
(ReportingTable.InstallDate BETWEEN dbo.ufn_FormDates_ReportWeekBeginningDate() AND dbo.ufn_FormDates_ReportWeekEndingDate())
AND (ReportingTable.Product = 'dsl'))
ORDER BY MarketSegmentDescriptions.[Description], OrderType

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Hi there,

I'm not sure exactly what you are asking. I think you might be looking for something like this:

insert into MyPivotTable (OrderType, AccountType, ...)

select ...

Hope this helps

Wayne

Dyan Sharp SSC Rookie Points: 27 More actions · Answer 2

I guess what I am saying is after the report runs from the proc and it is uploaded to our SQL Server Reporting Services on the website, our internal customers need to go through 256 pages of data to get what they need. I would like to create a Pivot Table that can be uploaded and easier for our customers to access. Is this possible with the proc that has been created?

I appreciate your help.