|
View previous topic :: View next topic
|
| Author |
Message |
revdpoel
New User
Joined: 01 Nov 2006
Posts: 56
|
|
|
|
Hai
i have a table A that has three fields
terminal-id
product-id
office-number
terminal/product is primary key
How can I quickly find out if there is more than one office-number related to a terminal-id?
example
row 1
terminal A
product GTA
office 1245
row 2
terminal A
product SDG
office 1249
Here two offices are related to the same terminal
thnx |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
revdpoel,
Try this,
| Code: |
select terminalid, count(*)
from table
group by terminalid
having count(*) > 1 |
Thanks,
sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Sushanth,
What if there were 2 rows with the same office# and multiple products? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi D.sch.
This can do that,
| Code: |
SELECT officeno,
REPLACE(
REPLACE(
REPLACE(
CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME X , productid))) AS VARCHAR(200))
,'<X>','')
,'</X>',' ')
,'<X/>',' ')
FROM tbqual.tablename
GROUP BY officeno
;
|
revdpoel, this is for you
| Code: |
SELECT terminalid,
REPLACE(
REPLACE(
REPLACE(
CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME X , officeno))) AS VARCHAR(200))
,'<X>','')
,'</X>',' ')
,'<X/>',' ')
FROM tbqual.tablename
GROUP BY terminalid
;
|
(USED XML just to do row to column transformation)
Thanks,
Sushanth |
|
| Back to top |
|
|
|
|
