Pivot SQL query output

Question

I have a requirement where my query will return output something like this:

PermissionType  IsAllowed
-------------------------
IsEdit        |  1
IsDelete      |  0
isRemove      |  1
isPrint       |  1
isReport      |  0
--            |  -
--            |  -
--            |  -
--------------------------

These rows can be dynamic depending upon the filter criteria I will pass.

So now I want to convert the above resultset to the following:

IsEdit | IsDelete | IsRemove | IsPrint | IsReport | -- | -- | --
--------------------------------------------------------------------
1      | 0        | 1        | 1       | 0        | -  | -  | -

I tried to use the pivot here but it asks the Column Names to be pivoted into the output and this is dynamic in my case, also it needed an aggregate function for FOR but I don't have any calculation in my case.

Anyone please help me on this.

If you want a dynamic pivot, this might help. https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — JonTout, Oct 09 '18 at 09:46
@JonTout This is really helpful.I think I got what I wanted with this link. Thank you for saving my day. — Pratap Singh, Oct 09 '18 at 09:56

score 1 · Answer 1 · answered Oct 09 '18 at 09:58

Then try this Dynamic sql

IF OBJECT_ID('dbo.temp')IS NOT NULL
DROP TABLE temp
;WITH Cte(PermissionType, IsAllowed)
AS
(
SELECT 'IsEdit'   ,  1 UNION ALL
SELECT 'IsDelete' ,  0 UNION ALL
SELECT 'isRemove' ,  1 UNION ALL
SELECT 'isPrint'  ,  1 UNION ALL
SELECT 'isReport' ,  0
)
SELECT  *,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS Seq INTO 
temp FROM Cte

DECLARE @Sql nvarchar(max),
        @Sqlcol  nvarchar(max),
        @ISNULLSqlcol nvarchar(max)

SELECT  @Sqlcol=STUFF((SELECT   ', '+QUOTENAME(PermissionType) 
                FROM temp  ORDER BY Seq FOR XML PATH ('')),1,1,'')

SELECT  @ISNULLSqlcol=STUFF((SELECT   ', '+'MAX('+QUOTENAME(PermissionType) +') AS '+QUOTENAME(PermissionType)
                FROM temp ORDER BY Seq FOR XML PATH ('')),1,1,'')

SET @Sql='
        SELECT '+@ISNULLSqlcol+'
        FROM(
        SELECT * FROM temp
        )AS SRC
        PIVOT 
        (
        MAX(IsAllowed) FOR PermissionType IN ('+@Sqlcol+')
        ) AS PVT '
PRINT @Sql
EXEC (@Sql)

IsEdit  IsDelete    isRemove    isPrint isReport
--------------------------------------------------
1         0             1           1       0

score 0 · Accepted Answer · answered Oct 09 '18 at 10:07

With pivot and dynamic sql you can create a query that will include a different number of columns:

if OBJECT_ID('Test') is not null 
drop table [dbo].[Test] 

CREATE TABLE [dbo].[Test](PermissionType varchar(20), IsAllowed int)

insert into [dbo].[Test] values
 ('IsEdit'  , 1)
,('IsDelete', 0)
,('isRemove', 1)
,('isPrint' , 1)
,('isReport', 0)

--this variable holds all the dates that will become column names 
declare @permissionTypes nvarchar(max) = '' 

--this variable contains the TSQL dinamically generated
declare @sql nvarchar(max) = ''  

select @permissionTypes = @permissionTypes + ', ' + quotename(PermissionType) 
from [dbo].[Test]  

set @permissionTypes = RIGHT(@permissionTypes, len(@permissionTypes)-2) 

set @sql = concat(
 'select *
    from [dbo].[Test]  
    pivot 
    ( 
    max(isallowed) 
    for PermissionType in (', @permissionTypes, ') 
    ) piv '
 )
 exec(@sql)

Result:

Adding a new row:

insert into [dbo].[Test] values
 ('IsNew'  , 1)

Causes a new column to be created:

Pivot SQL query output

2 Answers2