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.

[Alex_French]

Were you successful with that self-join version? I think it needs some more clever logic in the joins and/or where clauses or it will have several kinds of duplicates- both rows and columns?

I can think of at least three approaches-
1) Self-join, which will require some clever join criteria and where clauses to avoid duplicates, and I haven't quite wrapped my head around it

2) Use SQL Server's PIVOT option as Nick suggested. I think the trick here is to start by figuring out a query that gives each MIKG row you're interested in, then PIVOT that. I think one working option for that starting point would be:

select
itemnum,
kg567,
(select count(*)
from hsi.keyrecorddata123 mikg2
where mikg2.itemnum = mikg.itemnum and mikg2.recordnum
) as row_number
from hsi.keyrecorddata123 mikg
where kg567 = '123'

and the full query with pivoting:

select *
from
(
select
itemnum,
kg567,
(select count(*)
from hsi.keyrecorddata185 mikg2
where mikg2.itemnum = mikg.itemnum and mikg2.recordnum
) as row_num
from hsi.keyrecorddata185 mikg
where kg567 = '123'
) mikgwithrownum

pivot
(
max(kg567) for row_num in ([1], [2], [3])
) as pivotmikg

3) If you don't care about the order (e.g. [123, null null] is the same as [null, 123, 123], then you could add a cheap trick on top of the group by I suggested initially:

SELECT
itemnum,
case where (count(567) >= 1) then '123' else null end as key1,
case where (count(567) >= 2) then '123' else null end as key2,
case where (count(567) >= 3) then '123' else null end as key3
FROM hsi.keyrecorddata123
WHERE kg567 = 'ABC'
GROUP BY itemnum

Hopefully those ideas help you or another reader. If you do at least find that they work as you expect, let me know and I'll move them to an Answer.