A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR : CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS v_recordset SYS_REFCURSOR; v_sql VARCHAR2(32767); v_cols_1 VARCHAR2(32767); v_cols_2 VARCHAR2(32767); BEGIN SELECT LISTAGG( ””||”level”||”’ AS “‘||”level”||'”‘ , ‘,’ ) WITHIN … Read more
You need to fix this: SELECT @listCol = STUFF(( SELECT distinct ‘], [‘ + [size] FROM #t FOR XML PATH(”) ), 1, 2, ”) + ‘]’ To return the columns in the right order. You might have to do something like this instead of using DISTINCT: SELECT [size] FROM #t GROUP BY [size] ORDER BY … Read more
We’ve done a lot of work similar to your example. We haven’t worried about SQL injenction, in part because we have complete and total control over the data being pivoted–there’s just no way malicious code could get through ETL into our data warehouse. Some thoughts and advice: Are you required to pivot with nvarcahr(500) columns? … Read more
Unfortunately, MySQL does not have a PIVOT function but you can model it using an aggregate function and a CASE statement. For a dynamic version, you will need to use prepared statements: SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘max(case when part_type=””‘, part_type, ”’ then part_id end) AS part_’, part_type, ‘_id’ ) ) INTO @sql … Read more
You will want to use a PIVOT function for this. If you have a known number of columns, then you can hard-code the values: select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne] from ( select c.name, cr.description, r.typeid from customers c left join rewards r on c.id = r.customerid left join customerrewards cr on r.typeid = … Read more
You cannot put a dynamic statement in the PIVOT’s IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement. First, here is my sample table; myNumber myValue myLetter ———- ———- ——– 1 2 A 1 4 B 2 6 … Read more