StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing @DECLARE in MYSQL 5.1
text
Body
copied!<p>just playing around with some sql statements on MYSQL Server 5.1 I already asked a question how to make a specific count on two tables (see <a href="https://stackoverflow.com/questions/15807635/sql-count-over-2-tables-by-a-type/15807676?noredirect=1#comment22483634_15807676">here</a>) and I also found an answer how to transpose my result (see <a href="https://stackoverflow.com/questions/8429442/transposing-a-table-in-sql-server/14308741?noredirect=1#comment22486399_14308741">here</a>) but I cant use it on my local MYSQL Server 5.1.</p> <p>This is table one: test</p> <pre><code>id|name|test_type ------------- 1|FirstUnit|1 2|FirstWeb|2 3|SecondUnit|1 </code></pre> <p>The second table: test_type </p> <pre><code>id|type -------- 1|UnitTest 2|WebTest </code></pre> <p>The following result will be written into "yourtable" (a temporary table)</p> <pre><code>type|amount ----------- UnitTest|2 WebTest|1 </code></pre> <p>What I want to have at the end is:</p> <pre><code>UnitTest|WebTest ----------------- 2|1 </code></pre> <p>(The problem is I think, the the last part is from an MS-SQL example, so it will not work on MYSQL)</p> <p>This are my sql statements:</p> <pre><code>--create a temporary table create temporary table IF NOT EXISTS yourtable ( test_type varchar(255), amount varchar(255) ); --make a selecten into the temporary table INSERT INTO yourtable SELECT t.test_type , COUNT(*) AS amount FROM test_types AS t JOIN test AS te ON t.id= te.test_type GROUP BY test_type ORDER BY t.test_type; --just for debugging select * from yourtable; -- transpose result DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type) from yourtable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT ' + @cols + ' from ( select Type, Amount, row_number() over(partition by Type order by Type, Amount) rn from yourtable ) x pivot ( max(Amount) for Type in (' + @cols + ') ) p ' execute(@query) --drop temporary table drop table yourtable; </code></pre> <p>I can't run the last part where I want to transpose my temporary result. I get an error for "DECLARE" </p> <blockquote> <p>/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)</p> <p>select @cols = ' at line 2 <em>/ /</em> 2 rows affected, 2 rows found. Duration for 3 of 4 queries: 0,000 sec. */</p> </blockquote> <p>Can anybody help?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload