SQL with PIVOT (205 Views)
Reply
Occasional Contributor
chvk
Posts: 4
Registered: ‎12-10-2013
Message 1 of 4 (205 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!

Please use plain text.
Honored Contributor
Trudy Claspill
Posts: 3,354
Registered: ‎09-09-2010
Message 2 of 4 (172 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.)
Please use plain text.
Honored Contributor
Trudy Claspill
Posts: 3,354
Registered: ‎09-09-2010
Message 3 of 4 (158 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.)
Please use plain text.
Occasional Contributor
chvk
Posts: 4
Registered: ‎12-10-2013
Message 4 of 4 (115 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.
Please use plain text.
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