SQL Query to find the multiple values for same KW

[Anil_Patel1]

Hello,

How can I get the report or query to see all the possible / exiting values for keyword if that key word has multiple values?

I can execute the search like show me all docs created in last two days (assuming that some of the docs have multiple values (F6) for the KW, when I extract the KW through List report off the search result, I can see only first value of that KW, I need to know all the values for that KW

Your help is greatly appreciated

Thanks

Anil

[Anil_Patel1]

Thanks John/Greg,

it does work partially (no errors) but i need to list these reviewer names along with the count whic is under  hsi.keyitem1394 table

SELECT kw.itemnum, Count(kw.itemnum) AS 'numkeys' FROM hsi.itemdata it with(nolock) inner join hsi.keyitem1394 kw with(nolock) on kw.itemnum=it.itemnum WHERE it.status = 0 and it.itemtypenum=453 GROUP BY kw.itemnum HAVING (Count(kw.itemnum)>1)

i got following as a result of above query

18987780     5

 19888517    5

 20354835    5

 20396295    5

Sorry, i am not an expert on SQL, please help

Thanks

Anil

[Phil_Heineman1]

select i.itemname AS [Document], k.keyvaluechar AS [Reviewer]

from hsi.itemdata i inner join hsi.keyitem1394 k on i.itemnum = k.itemnum

order by i.itemnum

-- try this to align with your desired output.

-- Hope this helps!

[Anil_Patel1]

Thank you so much for your help

Anil

[Greg_Ledford]

Anil - The previous query doesn't work for what you need because you cannot list each itemnum, for each Reviewer and only count each Reviewer. So if you only need to know the names of each Reviewer, for each document type - and not the itemnums, then Philip's answer will suffice.

[Erik_Newman]

Anil,

As I've previously demonstrated elsewhere, this can be done. Just add this SQL using the inbuilt stuff() function as an extra column to your query to get the value of your Reviewers.

stuff(	(SELECT		'/' + rtrim(KEYITEM1394.keyvaluechar)	FROM		HSI.KEYITEM1394	WHERE		KEYITEM1394.itemnum = KW.itemnum	GROUP BY		KEYITEM1394.keyvaluechar	FOR		XML PATH('')), 1, 1, '') as keywords

You should get something like this

1234567 6 Reviewer1/Reviewer2/Reviewer3