Problem with Sql Query

By Running a query, I got the result set as shown below

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below and no. of rows for Status is unknown. it may be more than three like (I, O, S, T, W and so on), so dynamically the there will be more columns.

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

RG

Reminds me of the Cross-Tab queries in MS-Access.

Check -

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

'Almost' dynamic column generation.

|||I believe the new pivot functionality in sql server 2005/ express will do that for ya.sql

Problem with simple subquery in SQL2005 AND SQL2000.

When I use the simple query with a subquery shown below, this is the error message I get in SQL 2000 AND SQL 2005

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

And here is the query I use:

SELECT docSections.SectionID,

(SELECT docSectionText.colText FROM docSectionText

WHERE (docSections.SectionID = docSectionText.SectionID)

AND (docSectionText.colOrdinal = 1)) AS SecTitle

FROM docSections

Can anyone please let me know what I do wrong here.

Thanks

Gerhard

I can tell you why you get the error. But, without understanding your schema and requirements, I can not give you a solution for what you are trying to do.

The problem is that when you have a subquery in your SELECT it can only return 1 row per row. So, your subquery must be returning multiple rows.

To check try the following queries and see what it returns.

-- This should show you the sectionid that have multiple rows with colOrdinal = 1
SELECT SECTIONID, count(coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(coltext) > 1

-- This should checks if perhaps the identified sections have multiple rows
--but all with same coltext. If first returns rows, but this doesn't,
--then you can add distinct to solve your problems
SELECT SECTIONID, count(distinct coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(distinct coltext) > 1

HTH

|||

Thank You very much.

You were correct. I had 2 doubles in my table.

Gerhard