Pandas crosstab in SQL

Question

I'm struggling with implementing pandas crosstab function in an SQL script. I have a table which looks like this:

User   | Code   |  Used  
user1  | <null> |   1    
user2  | abca   |   4
user2  | <null> |   2
---
userN  | baaa   |   3

My goal is a table like this:

       | <null> |  abca  |  baaa  
user1  |   1    |   0    |   0    
user2  |   2    |   4    |   0
---
userN  |   0    |   0    |   1

So far I used this code, taken from here, but it returns an empty table:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= User FROM temp2

SET @DynamicPivotQuery = 
    N'SELECT * from (
    SELECT User, Code, Used
    FROM temp2) as src
    PIVOT
    (
        sum(Used) as sum FOR Code IN (' + @ColumnName + ')
    ) as piv'
EXEC sp_executesql @DynamicPivotQuery
SELECT @DynamicPivotQuery

There are literally hundreds of codes used, so apparently I need to use a dynamic pivot table, so I don't have to list all the codes. Zero values need to remain. I tried pretty much everything I could find on the Internet and StackOverflow, including this, this and many more. I'd appreciate any leads.

Does your row have the `varchar` value `''` or the (unknown) value `NULL` here? — Thom A, Apr 29 '19 at 15:49
@Wen-Ben The OP appears to be looking for a T-SQL solution here, not pandas. — Thom A, Apr 29 '19 at 15:58
You have tons of problems with your code. It's not even running as you posted. — Luis Cazares, Apr 29 '19 at 15:59
Guessing that's what they are using @Wen-Ben. People do seem to "over tag" questions; there's loads of questions tagged with `sql-server` yet the question is actually about C# (it's tagged `c#` too), purely on the basis that that's where the OP's data is and actually SQL Server has nothing to do with the question or problem at hand. — Thom A, Apr 29 '19 at 16:08

score 1 · Accepted Answer · answered Apr 29 '19 at 16:14

Assuming your value is NULL and not '<null>' then this seems to get you what you're after:

CREATE TABLE dbo.SampleTable ([User] varchar(6), --I suggest a different name here, USER is a reserved keyword in T-SQL
                               Code varchar(4),
                               Used tinyint);
INSERT INTO dbo.SampleTable ([User],
                              Code,
                              Used)
VALUES ('user1','null',1),    
       ('user2','abca',4),
       ('user2','null',2),
       ('userN','baaa',3);
GO

DECLARE @SQL nvarchar(MAX);
DECLARE @NL nchar(2) = NCHAR(13) + NCHAR(10)

SET @SQL = N'SELECT [User],' + @NL + 
           STUFF((SELECT N',' + @NL +
                         N'       MAX(CASE WHEN Code = ' + QUOTENAME(Code,'''') + N' THEN Used END) AS ' + QUOTENAME(COALESCE(Code,'null'))
                  FROM dbo.SampleTable ST
                  GROUP BY ST.Code
                  ORDER BY ST.Code
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,10,N'') + @NL + 
           N'FROM dbo.SampleTable' + @NL + 
           N'GROUP BY [User];';
PRINT @SQL;

EXEC sp_executesql @SQL;

GO

DROP TABLE dbo.SampleTable;

I've changed the method here, as I prefer building a Cross Tab to pivot data, and haven't used a variable to get the column values.

Hi again Larnu, could you perhaps show me what to change in the code to transpose the resulting table (turn rows to cols and cols to rows?) I'm a bit stuck here so I'd really appreciate some help. — Ian Shulman, May 02 '19 at 07:32
Turning rows to Columns, and columns to rows are very different answers. If you want to do the reverse, there are 100's of answers on SO on how to do this. if you fail, then ask a new question, showing your attempts, the questions you used, and explain why it didn't work. — Thom A, May 02 '19 at 08:08

score 1 · Answer 2 · answered Apr 29 '19 at 16:20

PIVOT is not able to handle NULL values as groups. That's why cross tabs is preferred in this case. You also have to change your condition to handle the comparison and avoid having further problems.

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT [User] ' + NCHAR(10) 
         + ( SELECT ',SUM( CASE WHEN Code ' + ISNULL( '= ' + QUOTENAME( Code, ''''), 'IS NULL') + ' THEN Used ELSE 0 END) AS ' + QUOTENAME(ISNULL( Code, 'NULL')) + CHAR(10)
            FROM temp2 
            GROUP BY Code
            FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)')
         + N'FROM temp2' + NCHAR(10) 
         + N'GROUP BY [User];'
PRINT @SQL;
EXEC sp_executesql @SQL;

Pandas crosstab in SQL

2 Answers2