448,548 Members | 1,155 Online
Need help? Post your question and get tips & solutions from a community of 448,548 IT Pros & Developers. It's quick & easy.

# Merging 2 columns from the same table

 P: n/a Hi All, This is what I'm trying to accomplish: I have a table T1 with the following fields - [UID] - [OrigUID] - [Type] (where type can be either B or C) | UID | OrigUID | Type | +-----+---------+------+ U1 | | B | U1 | U1 | C | U3 | U1 | C | U3 | U2 | C | U3 | U2 | C | What I want to do is to get: - the count of B in [Type] as [B count]for each distinct [UID] - the count of C in [Type] as [C count]for each distinct [OrigUID] - [All UID] where it combines the distinct [UID] and distinct [OrigUID] | All UID | B count | C Count | +---------+---------+---------+ U1 | 1 | 1 | U2 | 0 | 2 | U3 | 0 | 0 | What I have coded right now is: SELECT T1.[UID], (Select Count(*) FROM T1 AS test WHERE (test.[Type]='B') and (test.[UID] = T1.[UID])) AS [B Count], (SELECT Count(*) FROM T1 AS test1 WHERE (test1.[Type]='C') and (test1.[origUID] = T1.[origUID])) AS [C Count] FROM Table GROUP BY T1.[UID], T1.[origUID]; The results I got: | All UID | B count | C Count | +---------+---------+---------+ U1 | 1 | 0 | U1 | 1 | 2 | U3 | 0 | 2 | U3 | 0 | 2 | Please advise! TIA! regards, Nov 13 '05 #1