sql multi-instances

[Jenny_Le1]

Hello,

I have a Keyword Type Group (hsi.keyrecorddata134), in this KTG got 4 keywords ( i.e. Keyword1, Keyword2, Keyword3, Keyword3). 

I want to be able to run the report on this KTG pulling Keyword3, I can do something like this below:

select *
from hsi.keyrecorddata134
where Keyword3 in ('ABC')

But the problem I am struggling is, this KTG can be multi-instance, i.e. lets say this KTG got 3 multi-instances, which mean it'll have 3 Keyword3...sometime all three instances (keyword3) will not have the "ABC" value it in. Other time it will have something like Keyword3 = "ABC", Keyword3 = "DEF" and Keyword3 = "ABC"

or Keyword3 = "BLAH", Keyword3 = "DEF" and Keyword3 = "BLA" etc...

So I want to be able to only pull value "ABC"  with the result format like this:

Keyword1	Keyword2	Keyword3	Keyword3.2
text	text	ABC	ABC

When I tried the simple query above, it returns what I want but into separate rows. I tried two different select and inner join it still returns with two separate rows.

Thanks in advance for your help.

[Nick_Hoyle]

Sounds like you might be able to leverage a pivot function in your query but potentially a long shot. 

[Alex_French]

I can think of at least three questions about the data and your goal that would affect possible answers:

1) If Doc 1 has multiple MIKG rows, are Keyword1 and Keyword2 *always* the same across each MIKG row?

2) Do you know for sure what the maximum number of MIKG rows per document is?
2A) Do you really want:
-separate columns all returning the same value (as shown)
-or do you want a count of the number of occurrences of 'ABC'

3) Do you want to produce data grouped by document (my first assumption) or do you actually want to know about all unique combinations that exist? Your example didn't join to a table like itemdata, but maybe that's just keeping the psuedo-SQL simple.

If the answer to (1) is "yes", and the answer to (2) is "I really just want a count", and the answer to 3 is "group by document" and/or "join to other document information", then you might be looking for something like this (still in psuedo-SQL in terms of column names):

SELECT
itemnum,
keyword1,
keyword2,
count(*)
FROM hsi.keyrecorddata123
WHERE keyword3 = 'ABC'
GROUP BY itemnum, keyword1, keyword2

[Jenny_Le1]

Hi Alex,

1. No, Keyword1, Keyword2, Keyword3 will never be the same. Keyword1 and 2 is not important
2. Yes, the document only allow 3 instances
2A. Yes I want to separate columns format
3. Yes, it'll be grouped by itemnum. I just want to get the basic out of the way before I start joining too much that's why I didn't include in the example.

Thanks!

[Jenny_Le1]

I think I've got it to work the way I wanted it.
I'm not an sql expert so it it may not look pretty but at least it a start for me.
select
from
( select hsi.keyrecorddata123 k1
from
inner join hsi.keyrecorddata123 k2
where k1.keyword3 in ('ABC') or k2.keyword3 in ('ABC')
) groupA
left outer join
(select
from hsi.keyrecorddata123 k3
where keyword3 in ('ABC')
) groupB
on groupa.itemnum=groupb.itemnum