Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to simplify MSSQL query compatible to HIVE query ?

How to simplify MSSQL query compatible to HIVE query ?

Explorer

I have query in sql server which I want to simplify so that it can be Hive compatible. This is the query written in SQL format

SELECT session_id, 
       Substring ((SELECT ( ';' + tag_name ) 
               FROM   session_tag st2 
               WHERE  st2.session_id = st.session_id 
               FOR xml path ( '' )), 2, 1000) AS tags 
FROM   session_tag 
GROUP  BY session_id;
So I tried to simplify these query to get the same result the first query shows nothing
SELECT SUBSTRING(';'+tag_name,2,1000) as tag from session_tag st1 
where st1.session_id = (select st2.session_id from session_tag st2 where st1.session_id = st2.session_id for xml path (''))
Don't have an account?
Coming from Hortonworks? Activate your account here