SQL with PIVOT (326 Views)
Reply
Occasional Contributor
chvk
Posts: 4
Registered: ‎12-10-2013
Message 1 of 4 (326 Views)
Accepted Solution

SQL with PIVOT

Good afternoon:

 

i am having trouble running the below query with PIVOT in QC ALM 11.5 query builder. It say's "quality center couldn't run the query because it contains invalid statements". The purpose of this query is to display the bug records in a single row.

 

DECLARE @QUERY VARCHAR(4000)
DECLARE @BUG_ID VARCHAR(2000)
SELECT @BUG_ID = STUFF((SELECT
'],[' + STR(BUG.BG_BUG_ID)
FROM BUG
WHERE BUG.BG_SEVERITY = '1 - Critical'
AND BUG.BG_STATUS <> 'Fixed'
OR BUG.BG_SEVERITY = '2 - High'
AND BUG.BG_STATUS <> 'Fixed'
ORDER BY '],[' + STR(BUG.BG_BUG_ID)
FOR XML PATH('')
), 1, 2, '') + ']'

SET @QUERY =
'SELECT * FROM
(SELECT BUG.BG_BUG_ID
FROM BUG
) T
PIVOT (AVG(BUG.BG_BUG_ID) FOR BUG.BG_BUG_ID
IN ('+@BUG_ID+')) AS PVT'
EXECUTE (@QUERY)

 

Appreciate your help!

 

Thank You!

Honored Contributor
Trudy Claspill
Posts: 3,765
Registered: ‎09-09-2010
Message 2 of 4 (293 Views)

Re: SQL with PIVOT

There are certain key words that aren't allowed in SQL statements in Excel reports in ALM. I don't remember exactly what they are, but they are called out in the User Guide in the section about creating Excel reports. I think there might also be a way to override that, which might also be in the User Guide. Take a look there and see if that answers your question.

There were also some commands that simply could not be used (as of v11.0) I think PIVOT might be one of them. I'll have to review my archives when I'm back in office, and then maybe I can provide more information.
[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Honored Contributor
Trudy Claspill
Posts: 3,765
Registered: ‎09-09-2010
Message 3 of 4 (279 Views)

Re: SQL with PIVOT

The key words that normally can't be used, and how you can override that, is documented in the User Guide in the chapter on Excel reports under the heading "Protecting the Database". It doesn't call out PIVOT, but it does call out SET and EXECUTE which are both in your query.
[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Occasional Contributor
chvk
Posts: 4
Registered: ‎12-10-2013
Message 4 of 4 (236 Views)

Re: SQL with PIVOT

Hi Trudy - Thanks very much for helping me with this..I took off after posting this question so couldn't respond to you immediately.

Thanks, Vijay.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.