December 7, 2009 at 9:12 am
I need a help. I don't know who function i got to use.
I have the sample structure
Table client – id, name (varchar)
Table trademark - id,name
Table application - id,name
Table destination - id,name
Table association – id_cliente (fk client), type (defines trademark, application, destination), and idtype (fk to any relationed table - trademark, application, destination)
In the table association, i have the keys association for those tables
Client | type | idtype (id of eache table trademark, application, destination |
1 | 1 |1
1 |1 |5
1 |2 |3
1 |3 |10
joins result in more than a line
select c.name as CLIENT
, t.name as trade
, a.name as aplication
, d.name from client as c
inner join association as at on (c.id=at.cliente)
left join trademark as at on (t.id=at.idtype where type = 1)
left join application as at on (c.id=at.cliente where type = 2)
left join destination as at on (c.id=at.cliente where type = 3)
Client |Trademark | Aplication |Destination
Lucas silva |SEMAR | NULL |NULL
Lucas silva |RADS | NULL |NULL
Lucas silva |NULL | Diário |NULL
Lucas silva |NULL | NULL |LOG
I need to put in a sigle row some like above, listing the very first result of each type.
I tried to use coalesce function, but does not work fine.
Ex
Client |TradeMark | Aplication |Destination
Lucas silva |SEMAR | Diário |LOG
Can anybody help me?
best regards
Valmir
December 7, 2009 at 12:19 pm
Can you post the DDL and DML scripts in readily consumable format...
December 8, 2009 at 8:24 am
I think you want a crosstab/pivot. Check out the 2 articles in my signature about crosstabs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply