How to make pivot table of a non numeric table in Microsoft SQL Server?-CodePudding

I wanted to make a query and transform it to a pivot table in my back-end (MSQL Server and Django). I prefer to do it in SQL instead of Python (I cannot install Pandas). Can you please help? All things I found was for numeric values. If it's not possible to do it in SQL, helps regarding convert it in Python will also be much appreciated. Thanks

My data:

I want this:

CodePudding user response：

Use dynamic pivot like below to achieve desired result.

declare @columns varchar(max)='',@sql nvarchar(max);

SELECT @columns =QUOTENAME(flag_type)   ',' from (select distinct flag_type from 
YourTableName) t
SET @columns = LEFT(@columns, LEN(@columns) - 1);

set @sql='
SELECT *  FROM   
(
select items_col,flag_type,flag_value from YourTableName
) t 
PIVOT(Max(flag_value)
FOR flag_type IN (' @columns ')
) AS pivot_table
'

EXEC (@Sql)

CodePudding user response：

You can try this:

https://github.com/pivot-my-stuff/easy_pivot

I created a SQL file in the "examples" folder that pivots your data. This will probably only output to your SQL Server Management Studio display since the dynamic query runs as a subprocess. It sounds like you need more automation with the returned data. But I offer it in case you find it useful.

However... you can run Easy Pivot with a flag to output the SQL code:

DECLARE @generate_source_code_only AS BIT = 1

If you expect no new flag types, then you can probably modify the SQL code it generates and automate with that. You would just need to change the temporary table in the FROM section of this code to match the name of the actual table that holds the pre-pivoted data.