x

Dynamic SQL questions

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
more ▼

asked Oct 31, 2009 at 04:12 PM in Default

user-327 (yahoo) gravatar image

user-327 (yahoo)
1 1 1 1

You should look at the UNPIVOT command in T-SQL. It's available in SQL Server 2005 and above.
Oct 31, 2009 at 04:47 PM Benjamin
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

--sample data
declare @t table (Company varchar(10), timeid varchar(3), category varchar(3), volume int, grossmargin int)
insert @t
select 'c1000', 'jan', 'act', 100, 1000 union all
select 'C1000', 'feb', 'act', 300, 3000

--cross join method
select t.company
,t.timeid
,t.category
,c.account
,case c.account
when 'volume' then t.volume
when 'grossmargin' then t.grossmargin
end as amount
from @t t
cross join (select 'volume' account union all select 'grossmargin') c

--unpivot method
select company
,timeid
,category
,account
,amount
from @t
unpivot (
amount for account in ([volume],[grossmargin])
) up

output:
company timeid category account amount
---------- ------ -------- ----------- -----------
c1000 jan act volume 100
c1000 jan act grossmargin 1000
C1000 feb act volume 300
C1000 feb act grossmargin 3000
more ▼

answered Nov 02, 2009 at 03:36 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x374

asked: Oct 31, 2009 at 04:12 PM

Seen: 1002 times

Last Updated: Nov 11, 2009 at 06:02 AM