| Author |
Topic |
|
Ic0n
Starting Member
18 Posts |
 Posted - 2002-10-14 : 19:17:40
|
| I have 3 tables and i need to get a single row recordset back from a query passing an ID in as a parameter. [url]http://www.clanxtc.co.uk/db.gif[/url] see pic for DB layout.All i want back from the query is : MemberID, Member_Username and the SquadID's that the member is in. So if the member was in 3 squads i would get a single row with:-MemberID | Member_Username | Squad1 | Squad2 | Squad3 |for 1 squad i would get :-MemberID | Member_Username | Squad1 |Is this possible?? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-14 : 19:44:49
|
| Thanks for the reply but that article is way over my head, i tried it and all i get are errors. Could you give me some examples? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-14 : 19:53:19
|
| EXECUTE crosstab 'select M.MemberID, M.Member_UserName FROM Members M INNER JOIN Squad_Members S ON (S.Member_ID=M.Member_ID) WHERE M.Member_ID=' + CAST(@memberid AS varchar) + 'GROUP BY M.MemberID, M.Member_UserName', 'max(S.Squad_ID)', 'Squad_ID', 'Squads'This will pivot every squad though. If you only want the three or so squads, you need to use the modified version of the code posted in the comments section of the article:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216That accepts a WHERE clause for the pivoted columns.Edited by - robvolk on 10/14/2002 19:54:34 |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-14 : 20:18:14
|
| Thanks Lads!!That works perfectly, just what i needed :) |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-15 : 12:09:12
|
| Run into a slight problem. The query works perfectly on my development server but when i move it to my production box i get an error :-Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'END'.Any ideas what this could be?Edited by - Ic0n on 10/15/2002 12:09:33 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 12:19:37
|
| Too much data, too many values being pivoted. If there are more than about 200 pivot columns it will exceed the 8000 character limit for a varchar variable, and the entire SQL statement cannot be parsed or executed. Basically the code got cut off at the beginning or end of a CASE expression and that's what generated the error.Check the comments section, someone has a workaround that allows for larger SQL statements to be used with the cross-tab procedure. Or, use the other version I mentioned that uses a WHERE clause to restrict the number of pivoted columns. |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-15 : 13:08:28
|
| The data on both machines is identical so i dont understand why it works on my local machine but not on the other?? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 14:13:34
|
| You'd have to post the exact SQL statement generated. Change the code so that the EXEC (@select) line is SELECT @select instead, and examine the string in query analyzer (you will probably have to increase the maximum character setting to 8000 characters, this is under the Tools:Options menu on the Results tab) Try copying and pasting the generated SQL into the query window and executing it, it might be more precise as to where the error occurs. |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-15 : 14:27:02
|
| Statement generated :-SELECT A.MemberID As MemberID, A.Member_UserName As Member_UserName, 'q2dm' = count(A.MemberID END), 'q3dm' = count(A.MemberID END), 'ra2' = count(A.MemberID END) FROM Members A INNER JOIN Squad_Members B On A.MemberID = B.MemberID INNER JOIN Squads C ON B.SquadID = C.SquadID GROUP BY A.MemberID, A.Member_UserName |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 14:45:56
|
| Something's amiss, the code should generate something like:SELECT A.MemberID As MemberID, A.Member_UserName As Member_UserName, 'q2dm' = count(CASE A.MemberID WHEN 'q2dm' THEN A.MemberID END), 'q3dm' = count(CASE A.MemberID WHEN 'q3dm' THEN A.MemberID END), 'ra2' = count(CASE A.MemberID WHEN 'ra2' THEN A.MemberID END) FROM Members A INNER JOIN Squad_Members B On A.MemberID = B.MemberID INNER JOIN Squads C ON B.SquadID = C.SquadID GROUP BY A.MemberID, A.Member_UserNameThe original version of the code would generate this correctly (including the red sections), take a look to see that you're using EXACTLY the same procedure code as what was posted in the article. |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-15 : 21:52:07
|
| The SP on my dev box produces the code correctly, the production box has the exact SP that you use in the article yet produces errors. Does the Production box need any permission changes or should it just work as is? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 22:21:52
|
| The login under which the statement is executed needs CREATE TABLE permissions on tempdb. That's the only permission issue that's come up with this proc so far. |
|
|
|
Ic0n
Starting Member
18 Posts |
Posted - 2002-10-15 : 23:02:03
|
| Ok, how do i go about setting that up? :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 23:18:13
|
| Check Books Online under "GRANT" and "CREATE TABLE". |
|
|
|
|
Table Join Problem