suspress double fieldnames

[DanHD]

Hello

I've a simple query, like:

select 'PurchaseOrder'
, t0.DocNum
, t0.CardCode
, t1.VisOrder +1 as LineNr
, t1.ItemCode
from OPOR t0
inner join POR1 t1 on t0.DocEntry = t1.DocEntry

with the results:

DocNum CardCode LineNr ItemCode
------------- ----------- --------------- ----------- --------------------
PurchaseOrder 2012110155 112781V 1 130350042002
PurchaseOrder 2012110155 112781V 2 131250148202
PurchaseOrder 2012110155 112781V 3 072244903003
PurchaseOrder 2012110155 112781V 4 132250223501
PurchaseOrder 2012110156 162006V 1 132250472306

is it possible to suspress the double column values, like:

DocNum CardCode LineNr ItemCode
------------- ----------- --------------- ----------- --------------------
PurchaseOrder 2012110155 112781V 1 130350042002
2 131250148202
3 072244903003
4 132250223501
2012110156 162006V 1 132250472306

Dan

 

[dhulbert]

Not within SQL it isn't

If you are using a reportign tool such as Reporting Services or Cognos then you may well be able to.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[drlex]

Hmmm, I disagree - it is reasonably easy with Row_Number() function in SS2005.

Select case when rowNum = 1 then 'Purchase Order' + T0.DocNum else '' end as [field1], LineNr, ItemCode FROM
(select     t0.DocNum
        , t0.CardCode 
        , t1.VisOrder +1 as LineNr
        , t1.ItemCode
        , Row_NUMBER() OVER (partition by T0.DocNum ORDER BY T1.VisOrder) as rowNum
        from    OPOR t0
        inner join POR1 t1 on t0.DocEntry = t1.DocEntry) D1
order by D1.DocNum, D1.RowNum

(if I can understand the layout of the initial post, and DocNum is a string)


soi là, soi carré

 

[markros]

I agree with dhulbert - this needs to be done on the client, not in SQL Server.

PluralSight Learning Library

 

[DanHD]

Thanx drlex
this will help me.
(it is also possible to run querys directly in the user interface of the ERP software. So a report as CR is not always desired by the user)

Dan

 

[drlex]

Dan,
You're welcome. As the other posters have commented, it's usual to sort display matters in the application/client-side. However, there are occasions (e.g. html-formatted dbmail) when one needs to "tidy" up a results set within SQL server.

soi là, soi carré

 

[Olaf Doschke]

agreed and not agreed,

case when rowNum = 1 then 'Purchase Order' + T0.DocNum else '' end

is not that complicated, but many reporting tools will enable you to suppress repeating values and that keeps queries better readable and maintainable.

In this case PurchaseOrder looks as an overall caption to the whole list, why have it in any row at all? repeating DocNum is another issue, this asks for a treeview display with a Docnum node with ItemCodes as sub nodes.

Bye, Olaf.