SQL Pivot Table View - 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 2008 Forums

Transact-SQL (2008)

SQL Pivot Table View

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2011-12-14 : 10:20:47

I'm trying to create a pivot table from the following content:

OrderNum OrderLine InvoiceNum InvoiceLine
00006000434 00001 111608550 002
00006000434 00001 111608560 002
00006000434 00001 111608561 002
00006000434 00001 111608562 002

What I would like to have in my result is OrderNum, OrderLine, InvoiceNum1, InvoiceNum2, InvoiceNum3, InvoiceNum4, InvoiceLine

Each Order can only have a maximum of 4 Invoices but I need the results to be a single line per OrderNum and OrderLine as opposed to hwo it is displayed above and I cannot figure out how to get a pivot to work in this context.

Any help is greatly appreciated.

Thanks,

Wally

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 10:24:32

[code]
SELECT OrderNum,
OrderLine,
MAX(CASE WHEN Rn=1 THEN InvoiceNum END) AS InvoiceNum1,
MAX(CASE WHEN Rn=2 THEN InvoiceNum END) AS InvoiceNum2,
MAX(CASE WHEN Rn=3 THEN InvoiceNum END) AS InvoiceNum3,
MAX(CASE WHEN Rn=4 THEN InvoiceNum END) AS InvoiceNum4,
InvoiceLine
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY OrderNum,OrderLine,InvoiceLine ORDER BY InvoiceNum) AS Rn,*
FROM Table
)t
GROUP BY OrderNum,OrderLine,InvoiceLine
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wldodds
Starting Member

20 Posts

Posted - 2011-12-14 : 15:52:25

That worked well, I've made a couple of adjustments to reflect the real table, etc. Here is what I have:

SELECT ('INVCS'+ORDR_NUMBER) AS Sales_Doc_Num,
((cast(ORDR_LINE_NMBR as [numeric] (19,0)))*16384) as Order_Line_Num,
MAX(CASE WHEN Rn=1 THEN isnull(INVC_NUMBER,'') END) AS InvoiceNum1,
MAX(CASE WHEN Rn=2 THEN isnull(INVC_NUMBER,'') END) AS InvoiceNum2,
MAX(CASE WHEN Rn=3 THEN isnull(INVC_NUMBER,'') END) AS InvoiceNum3,
MAX(CASE WHEN Rn=4 THEN INVC_NUMBER END) AS InvoiceNum4,
INVC_LINE_NMBR
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ORDR_NUMBER,ORDR_LINE_NMBR,INVC_LINE_NMBR ORDER BY INVC_NUMBER) AS Rn,*
FROM _PMSI_INVOICES
)t
GROUP BY ORDR_NUMBER,ORDR_LINE_NMBR,INVC_LINE_NMBR

When I run this though it throws this message:

Warning: Null value is eliminated by an aggregate or other SET operation.

At the end of the return results. I was hoping the isnull function I added above was going to resolve it but it doesn't, any other ideas?

Thanks,
Wally

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 00:03:54

its just a warning and wont affect results much so you can ignore that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources