CODEWITHSUNDEEP
sqlt-sqlpivotcase
HansMusterWhatElse
HansMusterWhatElse

Reputation: 671

Pivot Table - Max() in Combination with Case

I've got the following query:

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ID_FK_Group) 
                    from [randomDb].[dbo].[table_GroupMembership]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [UserDisplayName], ' + @cols + ' from 
             (
                select u.DisplayName as [UserDisplayName], gm.ID_FK_Group as [ID_FK_Group], gm.ModDate as [ModDate]
                from [randomDb].[dbo].[table_user] u
                join [randomDb].[dbo].[table_GroupMembership] gm on u.id_pk_user = gm.id_fk_user
            ) x
            pivot 
            (
                MAX(CASE when [ID_FK_Group] != null THEN "x" ELSE " " END)
                for [ID_FK_Group] in (' + @cols + ')
            ) p '

execute(@query)

If I use MAX([ModDate]) instead of MAX(CASE when [ID_FK_Group] != null THEN "x" ELSE " " END) it works just fine but of course it then displays the moddate instead of a "x" in the pivot table.

I've tried using different combinations of the case statement in combination with MAX() but for some reason I always end up with the same error:

Incorrect syntax near the keyword 'CASE'.

What am I missing?

Upvotes: 1

Views: 866

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180947

It seems you can't pivot over a CASE statement, so you'll have to move the expression out into the query, something like;

SELECT [UserDisplayName], ' + @cols + ' from 
(
    select u.DisplayName as [UserDisplayName], gm.ID_FK_Group as [ID_FK_Group],
      CASE when [ID_FK_Group] is not null THEN 'x' ELSE ' ' END tmp
    from [randomDb].[dbo].[table_user] u
    join [randomDb].[dbo].[table_GroupMembership] gm on u.id_pk_user = gm.id_fk_user
) x
pivot 
(
    MAX(tmp)
    for [ID_FK_Group] in (' + @cols + ')
) p

Upvotes: 3

sagi
sagi

Reputation: 40481

You have incorrect syntax, when dealing with null values you should use IS NULL and IS NOT NULL , also you should use only one apostrophe and not two when using string values.

Change your case part to this:

MAX(CASE when [ID_FK_Group] is not null THEN 'x' ELSE ' ' END)

You can read about it here

Upvotes: 0

Related Questions