Removing duplicates from data

As a starter.....

select
    TA1.FieldA
    ,TB2.FieldB
    ,TC2.FieldC
from
    #TableA TA1
left join
    (
    select TB1.FieldA, TB1.FieldB, COUNT(*) as Seq 
    from #TableB TB1 join #TableB TB2 
    on TB1.FieldA = TB2.FieldA
    and TB1.FieldB >= TB2.FieldB
    group by TB1.FieldA, TB1.FieldB
    ) TB2
    on TA1.FieldA = TB2.FieldA
LEFT JOIN
    (  
    select TC1.FieldA, TC1.FieldC, COUNT(*) as Seq 
    from #TableC TC1 join #TableC TC2 
    on TC1.FieldA = TC2.FieldA
    and TC1.FieldC >= TC2.FieldC
    group by TC1.FieldA, TC1.FieldC
    ) TC2
    on TB2.FieldA = TC2.FieldA and TB2.Seq= TC2.seq

This pre-2005 solution creates a sequence number (or rank) by joining the table back onto itself and counting the number of rows with a value less than or equal to the current one.

---- Edit: going code-blind now...

does this give the right results

drop table #tally

SELECT TOP 1000 IDENTITY(INT,1,1) AS N
INTO #tally
FROM Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

drop table #tablea
drop table #tableb
drop table #tablec



CREATE TABLE #TableA
(iID INT IDENTITY,
FieldA INT )

CREATE TABLE #TableB
(FieldA int,
FieldB int)

CREATE Table #TableC
(FieldA int, FieldC int)

INSERT INTO #TableA
        ( FieldA )
SELECT 1
union
SELECT 2
UNION
SELECT 3

INSERT INTO #TableB
(FieldA, FieldB)
SELECT 1,1
UNION
SELECT 1,2
UNION 
SELECT 1,3
UNION 
SELECT 2,1
UNION
SELECT 2,2
UNION
SELECT 2,3
UNION 
SELECT 3,1
UNION
SELECT 3,2
UNION SELECT 3,3

INSERT INTO #TableC
(FieldA, FieldC)
SELECT 1,1
UNION
SELECT 1,2
UNION 
SELECT 1,3
UNION 
SELECT 2,1
UNION
SELECT 2,2
UNION
SELECT 2,3
UNION 
SELECT 3,1
UNION
SELECT 3,2
UNION SELECT 3,3

select
    TA3.FieldA,
    TB3.FieldB,
    TC3.FieldC

from
    #tally t full join #TableA TA3 on ta3.FieldA = ta3.FieldA
left join
    (
    select TB1.FieldA, TB1.FieldB, COUNT(*) as Seq 
    from #TableB TB1, #TableB TB2 
    where TB1.FieldA = TB2.FieldA
    and TB1.FieldB >= TB2.FieldB
    group by TB1.FieldA, TB1.FieldB
    ) TB3
    on TB3.FieldA = TA3.FieldA
    and TB3.Seq = N

left join
    (
    select TC1.FieldA, TC1.FieldC, COUNT(*) as Seq 
    from #TableC TC1 join #TableC TC2 
    on TC1.FieldA = TC2.FieldA
    and TC1.FieldC >= TC2.FieldC
    group by TC1.FieldA, TC1.FieldC
    ) TC3
    on TC3.FieldA = TA3.FieldA
    and TC3.Seq = t.N
where 
    TB3.FieldA is not null
    or
    TC3.FieldA is not null

Includes a pre-2005 tally table to give something common for all the tables to join on, even if there are differing numbers of rows.

OK, one option (edit - based on original query - edit) would appear to be create a fourth temporary table and select from that. So change your SELECT... statement to read:

SELECT DISTINCT TA.FieldA, TB.FieldB, TC.FieldC
INTO #TableD
FROM #TableA AS TA
LEFT OUTER JOIN #TableB AS TB ON TA.FieldA = TB.FieldA
LEFT OUTER JOIN #TableC AS TC ON TA.FieldA = TC.FieldA
WHERE TB.FieldB = TC.FieldC

Then use the following:

SELECT CASE WHEN FieldB=1 AND FieldC=1 
       THEN Convert(char(5), FieldA) 
       ELSE '' END AS FieldA, 
    FieldB, FieldC
FROM #TableD

select field1,field2,count(*) as count into #temp from tablename groupby field1,field2 truncate table tablename select field1,field2 into tablename from #temp

select field1,field2,count(*) as count into #temp from tablename groupby field1,field2

truncate table tablename

select field1,field2 into tablename from #temp