Crosstab using CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Crosstab using CASE

Author

Topic

vsdanner
Starting Member

6 Posts

Posted - 2007-03-22 : 19:21:17

My first post...pls be gentle. Any help is greatly appreciated.

I'm familiar with basic CASE statement to generate cross-tab results, but am having difficulty with this seemingly simple request.
I have invoice data in a table, such as:

INVOICE#
INVOICE_LINE#
PROMO_CODE
PROMO_AMT

I can have up to three records per invoice line item, one for each promo and amount. Promo's reflect an off-invoice promotion of some sort on any given item.

I want to consolidate this into a resultset of one record for each invoice line item. Problem is, I have no way of knowing ahead of time what those promo codes will be, so can't explicitly specify within a case statement. I do know there will never be more than three records (promos) for an invoice line item in the source table.

What I'm hoping to achieve within one record in resultset is:
INVOICE#
LINE NUMBER
PROMO1_CODE (Promo_Code for first promo)
PROMO1_AMT (Promo_Amt for first promo)
PROMO2_CODE (Promo_Code for second promo, if any)
PROMO2_AMT (Promo_Amt for second promo, if any)
PROMO3_CODE (Promo_Code for third promo, if any)
PROMO3_AMT (Promo_Amt for third promo, if any)

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 19:44:28

Post your table structure, sample data and the required result.

vsdanner
Starting Member

6 Posts

Posted - 2007-03-22 : 20:27:06

INVOICE# (INT), INVOICE_LINE# (SMALLINT), PROMO_CODE (CHAR (8)), PROMO_AMT (DECIMAL (11,2))

Sample from source table for invoice 587901:
INVOICE# INVOICE_LINE# PROMO_CODE PROMO_AMT
----------- ------------- ---------- -------------
587901 1 2936 7.20
587901 1 2937 1.92
587901 2 2936 76.80
587901 2 2937 19.20
587901 3 2936 45.60
587901 3 2937 9.60

So I want only one row for each in resultset. For example, for line item 1:
INVOICE# INVOICE_LINE# PROMO1_CODE PROMO1_AMT PROMO2_CODE PROMO2_AMT PROMO3_CODE PROMO3_AMT
587901 1 2936 7.2 2937 1.92

In this example promo codes are predictable, but we have thousands in reality. Each line item could have up to three records for a promo - this example there are only two.

Sorry if the format is funky...not sure how to insert a better table format for posting here.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 21:33:07

[code]
select [INVOICE#], [INVOICE_LINE#],
PROMO1_CODE = '2936',
PROMO1_AMT = sum(case when PROMO_CODE = '2936' then PROMO_AMT else 0 END),
PROMO2_CODE = '2937',
PROMO2_AMT = sum(case when PROMO_CODE = '2937' then PROMO_AMT else 0 END)
from yourtable
GROUP BY [INVOICE#], [INVOICE_LINE#]
[/code]

vsdanner
Starting Member

6 Posts

Posted - 2007-03-22 : 21:43:44

Thank you - my problem is, though, that I don't know what the promo Code will be in any given scenario. In the example I gave I do, but the promo code values are vast in possibilities.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 21:53:33

In that case you will have to use Dynamic SQL as the promo code is not pre-determine. And the code will not be easy.

Can you do this in the front end reporting tool ?

vsdanner
Starting Member

6 Posts

Posted - 2007-03-22 : 22:01:51

That's what I was afraid of. I was hoping there might be a way perhaps through creating a temp table with some value that would identify how to place promo and promo amount values in the respective columns by corresponding line item. I won't know at time of user query through front-end, since they won't know either - they just want to see all related info for that invoice. I could manipulate the multi-line recordset through the VB interface and align the values accordingly when exporting it all into the final output of Excel, I suppose.

Thanks very much for your time - very much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 22:09:17

Also how do you know how many promo code will be used per Invoice ? Is there a sort of max per invoice ?

vsdanner
Starting Member

6 Posts

Posted - 2007-03-22 : 23:29:26

Yes, it's pretty much a known business rule throughout our history, at least in terms of off-invoice promotions allowed per line item on an order, and this is evident through searching our entire historical invoice data. I've never known it to exceed two, but I allow for three.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 23:53:14

[code]
declare @table table
(
INVOICE# int,
INVOICE_LINE# int,
PROMO_CODE int,
PROMO_AMT decimal(10,2)
)

insert into @table
select 587901, 1, 2936, 7.20 union all
select 587901, 1, 2937, 1.92 union all
select 587901, 2, 2937, 19.20 union all
select 587901, 2, 2936, 76.80 union all
select 587901, 3, 2936, 45.60 union all
select 587901, 3, 2937, 9.60 union all
select 587902, 1, 2937, 9.60 union all
select 587902, 2, 2936, 12.36 union all
select 587902, 2, 2937, 12.37 union all
select 587902, 2, 2938, 12.38

select INVOICE#, INVOICE_LINE#,
PROMO1_CODE = max(case when PROMO_NO = 1 then PROMO_CODE end),
PROMO1_AMT = sum(case when PROMO_NO = 1 then PROMO_AMT end),
PROMO2_CODE = max(case when PROMO_NO = 2 then PROMO_CODE end),
PROMO2_AMT = sum(case when PROMO_NO = 2 then PROMO_AMT end),
PROMO3_CODE = max(case when PROMO_NO = 3 then PROMO_CODE end),
PROMO3_AMT = sum(case when PROMO_NO = 3 then PROMO_AMT end)
from
(
select INVOICE#, INVOICE_LINE#,
PROMO_NO = row_number() over (PARTITION BY INVOICE#, INVOICE_LINE# ORDER BY PROMO_CODE),
PROMO_CODE,
PROMO_AMT
from @table
) a
group by INVOICE#, INVOICE_LINE#
order by INVOICE#, INVOICE_LINE#

/*
INVOICE# INVOICE_LINE# PROMO1_CODE PROMO1_AMT PROMO2_CODE PROMO2_AMT PROMO3_CODE PROMO3_AMT
----------- ------------- ----------- ----------- ----------- ----------- ----------- -----------
587901 1 2936 7.20 2937 1.92 NULL NULL
587901 2 2936 76.80 2937 19.20 NULL NULL
587901 3 2936 45.60 2937 9.60 NULL NULL
587902 1 2937 9.60 NULL NULL NULL NULL
587902 2 2936 12.36 2937 12.37 2938 12.38
*/
[/code]

vsdanner
Starting Member

6 Posts

Posted - 2007-03-23 : 00:40:10

Interesting. Anxious to check this out more thoroughly in morning. There are some things here I've not used before which are especially interesting: row_number() over (PARTITION...

Thank you for all your work on this - it's been driving me crazy!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 00:42:19

row_number() is a new function only in 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources