Select only distinct values from duplicate rows

Question

I have a table with the following data:

client totalA totalB item# clientA 454265.08 869.5 307-1-3138 clientA 1110067.09 869.5 307-1-3138 clientB 15900 852.5 307-1-3149 clientC 436450.76 465 307-5-680 clientC 1066535.03 465 307-5-680

Notice that there are 2 rows for clientA and clientC, because the totalA column is different - because the amounts are displayed in 2 currencies - . What I need to return is 2 rows per currency, but the second row should only show the totalA column, and blank for the rest. So what I want is:

 client totalA totalB item# clientA 454265.08 869.5 307-1-3138 1110067.09 clientB 15900 852.5 307-1-3149 clientC 436450.76 465 307-5-680 1066535.03

Any suggestions how to do this in SQL Server 2008?

Thanks!

This is the job for a report designer/report writer - this is not something that a SQL database can do easily.... — marc_s
– marc_s, Commented Mar 17, 2011 at 18:09
marc_s: is it not at all possible? I know it might not be pretty, and definitely not the most efficient, but if those are not concerns, can this be done? — biggiesmalls
– biggiesmalls, Commented Mar 17, 2011 at 18:20
DKnight - if we assume only 2 currencies, what do you suggest? — biggiesmalls
– biggiesmalls, Commented Mar 17, 2011 at 18:20

Longha · Accepted Answer · 2011-03-17 18:42:59Z

SELECT Client, totalA, case when number = 1 then totalb else null end totalB, case when number = 1 then item else null end Item FROM ( select *, row_number() over(partition by client order by client) number from Clients ) A

marc_s · Accepted Answer · 2011-03-17 18:23:09Z

I would argue that your data isn't properly normalized - but there might be reasons for this.

On SQL Server 2005 and up, you could use a CTE to get a result close to what you're looking for - try this:

WITH ShowClients AS ( SELECT Client, TotalA, TotalB, ItemNo, ROW_NUMBER() OVER(PARTITION BY Client ORDER BY TotalA DESC) AS 'RowNo', ROW_NUMBER() OVER(ORDER BY Client,TotalA DESC) AS 'Sequence' FROM clients ) SELECT Client, TotalA, TotalB, ItemNo, Sequence FROM ShowClients WHERE RowNo = 1 UNION SELECT '', TotalA, NULL, NULL, Sequence FROM ShowClients WHERE RowNo > 1 ORDER BY Sequence

It gives me an output of:

Client TotalA TotalB ItemNo Sequence clientA 1110067.0900 869.5000 307-1-3138 1 454265.0800 NULL NULL 2 clientB 15900.0000 852.5000 307-1-3149 3 clientC 1066535.0300 465.0000 307-5-680 4 436450.7600 NULL NULL 5

Collectives™ on Stack Overflow

Select only distinct values from duplicate rows

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related