I have a table with the following format:
Company timeid category volume grossmargin c1000 jan act 100 1000 C1000 feb act 300 3000
I am trying to do a pivot where the end result of the query is
Company timeid category account amount C1000 jan act volume 100 C1000 feb act volume 300 C1000 jan act grossmargin 1000 C1000 feb act grossmargin 3000
In native sql I would write for each account the query as: Select company, timeid, category, 'volume' as account, volume as amount from table where volume <> 0
However, this is inefficient, I want to use variables to pass to the sql query the proper account and pull it's associated data since we have several hundred accts.
Here is the code I developed, but I keep getting can not convert nvarchar to int errors for @p1. Any suggestions would be greatly appreciated.
FYI, I am currenty setting @P1 from a secondary table
USE [Finance] GO /****** Object: StoredProcedure [dbo].[CustomerPLDataPrep] Script Date: 10/30/2009 11:45:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: Procedure to pull data from a temp table, scrub the data and write to a flat file for loading -- ============================================= BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON DECLARE @P1 AS varchar(25); SET @P1 = ' '; DECLARE @RECCUR INT SET @RECCUR = 0; DECLARE @ RECMAX INT SET @RECMAX = ( SELECT COUNT(*) FROM dbo.tmpACCOUNT ) ; DECLARE @SQL VARCHAR(4000); Truncate table [Finance].[dbo].[tmpCustPrepConsol] WHILE @RECCUR @P1 ) SUB) INSERT INTO [Finance].[dbo].[tmpCustPrepConsol] ([COMPANY] ,[PROFITCTR] ,[AREARESP] ,[PRODLINE] ,[TIME] ,[SCENARIO] ,[RPTCURRENCY] ,[DATASRC] ,[CUSTOMER] ,[GEOGRAPHY] ,[PAYER] ,[PLANTCUSTOMERS] ,[REGSALESACCT] ,[ACCOUNT] ,[AMOUNT] ,[SALES]) SELECT 'C'+COMP_CODE AS COMPANY , SUBSTRING(PROFIT_CTR, 4, 7) AS PROFITCTR, 'NO_AR' AS AREARESP , CASE WHEN LEN(MATERIAL)'' THEN RTRIM('S'+SALES) WHEN SUBSTRING(PROFIT_CTR, 4, 7)='2090050' THEN 'S1100' ELSE 'S1000' END AS SALES FROM Finance.dbo.tmpCustomers WHERE @P1 '' AND COMP_CODE ='1000' AND ( SALES='1000' OR SALES='1100' ) SET @RECCUR = @RECCUR + 1 SET NOCOUNT OFF END end
Here are a couple ways. No matter which method you use you can always generate the columns list code by SELECTing out of information_schema.columns.
answered Nov 02 '09 at 03:36 PM