CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
biggiesmalls
biggiesmalls

Reputation: 3

Select only distinct values from duplicate rows

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!

Upvotes: 0

Views: 596

Answers (2)

Longha
Longha

Reputation: 741

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

Upvotes: 2

marc_s
marc_s

Reputation: 754250

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

Upvotes: 1

Related Questions