Last post Jan 18, 2019 02:33 PM by limno
Member
34 Points
63 Posts
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>
Select PM.PageId, PM.PageName, ISNULL(UPA.IsAccess,0) AS IsAccess
FROM tblPageMaster PM LEFT JOIN tblUserPageAccess UPA ON UPA.PageId = PM.PageId AND UPA.UserId = 74
Output :
Required Output:
Please help me I am new in SQL Server, Thanks in advance Thanks KD
Contributor
7366 Points
1604 Posts
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
All-Star
123252 Points
10024 Posts
Moderator
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
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 :
Required Output:
Please help me I am new in SQL Server, Thanks in advance
Thanks
KD
Contributor
7366 Points
1604 Posts
Re: SQL Server Pivot Select Query
Jan 18, 2019 12:21 PM|me_ritz|LINK
All-Star
123252 Points
10024 Posts
Moderator
Re: SQL Server Pivot Select Query
Jan 18, 2019 02:33 PM|limno|LINK
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm