SQL Server Pivot Select QueryRSS

• • Reply
  krdobariya

  Member

  34 Points

  63 Posts

  SQL Server Pivot Select Query

  Jan 17, 2019 07:05 PM|krdobariya|LINK

  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

• • Reply
  me_ritz

  Contributor

  7366 Points

  1604 Posts

  Re: SQL Server Pivot Select Query

  Jan 18, 2019 12:21 PM|me_ritz|LINK

  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

  Please mark the post as "Answer" that helps you.
• • Reply
  limno

  All-Star

  123088 Points

  9964 Posts

  Moderator

  Re: SQL Server Pivot Select Query

  Jan 18, 2019 02:33 PM|limno|LINK

  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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm