locked
SQL Server Pivot Select Query RRS feed

  • Question

  • User-1803226828 posted

    Hello,

    I have select query, i want to convert this query to single row out put please suggest me how to do this.

    Select Query:

    <div>Select PM.PageId, PM.PageName, ISNULL(UPA.IsAccess,0) AS IsAccess</div> <div>FROM tblPageMaster PM  LEFT JOIN tblUserPageAccess UPA ON UPA.PageId = PM.PageId AND UPA.UserId = 74</div>

    Output :

    PageId PageName IsAccess
    1 Dashboard 1 1
    2 Dashboard 2 0
    3 Dashboard 3 1
    5 Dashboard 4 1
    N ------- N

    Required Output:

    Dashboard 1 Dashboard 2 Dashboard 3 Dashboard 4 --- ----
    1 0 1 1 1 1

    Please help me I am new in SQL Server, Thanks in advance

    Thanks
    KD

    Thursday, January 17, 2019 7:05 PM

All replies

  • User-2146987983 posted
    create table temp (PageId	int, PageName	varchar(100), IsAccess int)
    
    insert into temp 
    select 1,	'Dashboard 1',	1 union all
    select 2,	'Dashboard 2',	0 union all
    select 3,	'Dashboard 3',	1 union all
    select 5,	'Dashboard 4',	1
    
    select PageName, IsAccess into #t from temp
    
    
    DECLARE @paramList VARCHAR(MAX)
    SET @paramList = STUFF((
                         SELECT DISTINCT ',[' + PageName + ']'
                         FROM #t FOR XML PATH('')
                        )
                     ,1,1,'')
    
    PRINT @paramList
    
    DECLARE @query NVARCHAR(MAX)
    SET @query = 'SELECT ' + @paramList + ' FROM ( SELECT * FROM #t ) src
    PIVOT(SUM(IsAccess) FOR PageName IN (' + @paramList + ')) pvt'
     
    EXEC sp_executesql @query
    
    drop table #t
    drop table temp

    Friday, January 18, 2019 12:21 PM
  • User77042963 posted
    create table yourquery (PageId	int, PageName	varchar(100), IsAccess int)
    
    insert into yourquery values(1,'Dashboard 1',1)
    ,(2,'Dashboard 2',0)
    ,(3,'Dashboard 3',1)
    ,(5,'Dashboard 4',1)
     
     
     
     --dynamic pivot with CASE
     Declare @sql nvarchar(max);
    Declare @ColumnHeaders VARCHAR(MAX) 
    SET @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn=' + cast(rn as varchar(5)) 
    + ' THEN IsAccess else null end ) as ' + quotename('Dashboard'+Cast(rn as varchar(2)),'[')  + char(10)+char(13)
      FROM (Select * ,row_number() Over(Order by PageName) rn from yourquery) src
     FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     --print @ColumnHeaders
     
     set @sql = N'SELECT ' + @ColumnHeaders + '  FROM   (Select * ,row_number() Over(Order by PageName) rn from yourquery) src  ';
    --print @sql
    exec(@sql);
    
    drop table yourquery

    Friday, January 18, 2019 2:33 PM