Visual formatting of data returned from a SAP dat

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)

Visual formatting of data returned from a SAP dat

Author

Topic

Darts75
Starting Member

27 Posts

Posted - 2014-04-02 : 23:25:16

Hi Everyone,

I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.

Here is a sample of the data that I am getting back at the moment -

[url]https://flic.kr/p/mH6BDd[/url]

Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells) -

[url]https://flic.kr/p/mH6BLC[/url]

I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.

Here is my original code -


select
td.ItemCode as 'Item Code'
, td.Dscription as 'Item Description', td.Quantity as 'Order Qty'
, titm.OnOrder as 'PO Qty'
, th.CardCode as 'BP Code', th.CardName as 'BP Name'
, th.DocNum as 'Sales Order Number'
, tsp.SlpName as 'Sales Person'
, twhs.WhsCode as 'Whs Code'
, isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'

from AU.dbo.RDR1 td
left outer join AU.dbo.ORDR th on th.DocEntry = td.DocEntry
left outer join AU.dbo.OITM titm on titm.ItemCode = td.ItemCode
left outer join AU.dbo.OITW twhs on twhs.ItemCode = td.ItemCode
left outer join AU.dbo.OSLP tsp on tsp.SlpCode = td.SlpCode
left outer join A1Warehouse.dbo.BINLOCAT tbloc on tbloc.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS


where 
td.LineStatus = 'O'  and td.ItemCode = 'DR1124' and twhs.WhsCode like @whCode

order by td.ItemCode, th.DocNum

I am now trying to incorporate the previously mentioned functions with limited success.

https://flic.kr/p/mH818j

Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire?

If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.

[url]https://flic.kr/p/mH81cN[/url]

I am very new to MS SQL programming so I figure that I have probably made some 'largish' errors above. If anybody can help me correct my code (the longer the explanation the better) it will be much appreciated.

Kind Regards,

Davo

P.S.: Sorry about not embedding the pictures into the thread, I am using Flickr, and it insists on https.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources