Show value of particular column as Header in SQL Server

Admin 1
Viewed 2747

I am creating a web app in which I have a requirement where I want to display a column value as a header

Example

SELECT Name, Leave
FROM tblUser 
INNER JOIN tblLeaveMaster ON tblUser.EmployeeID = tblLeaveMaster.EmployeeID

From that query, I get these results:

Name     Leave
---------------
Test1       5
Test2      10
test3       2

Now I want to get these values as 

Test1 Test2 Test3
-----------------
  5     10    2

How can I achieve this?

pivot join tsql sql-server sql
5 Answers

You can try using pivot

select pv.* from
(SELECT Name,Leave
FROM tblUser INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
)X
pivot
(max(leave) for name in ([Test1],[Test2],[Test3])) as pv

For Dynamic PIVOT

declare @sql varchar(max)='',@col_list varchar(8000)=''

set @col_list = (select distinct quotename([Name])+',' from (SELECT Name,Leave
    FROM tblUser INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
    )X
for xml path(''))

set @col_list = left (@col_list,len(@col_list)-1)

set @sql = 'select '+@col_list+' from
    (SELECT Name,Leave
    FROM tblUser INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
    )X
pivot (max([Leave]) for [Name] in ('+@col_list+'))pv'

exec(@sql)
user_69148640

You can try to use condition aggregate function. CASE WHEN with MAX or MIN

SELECT 
    MAX(CASE WHEN Name = 'Test1' THEN Leave END) Test1,
    MAX(CASE WHEN Name = 'Test2' THEN Leave END) Test2,
    MAX(CASE WHEN Name = 'Test3' THEN Leave END) Test3
FROM tblUser 
INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID

EDIT

If your column want to create dynamic you can try to use Dynamic PIVOT

create your SQL statement and make condition aggregate function by connect SQL string. then use execute it Dynamically.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ', MAX(CASE WHEN Name = ''' + Name+''' THEN Leave END) ' + QUOTENAME(Name) 
            FROM tblUser 
            INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET  @query= 'SELECT '+ @cols+'
FROM tblUser 
INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID'

execute(@query)

sqlfiddle

user_51760710

try by using case when

select max( case when name='Test1' then Leave end) as test1,
max( case when name='Test2' then Leave end) as test2,
max( case when name='Test3' then Leave end) as test3 from 
tblUser INNER JOIN tblLeaveMaster
ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
user_68085830

You can find your result from the query as shown below. Here I have taken your query output in a temporary table.

Create table #finalData(ColName Varchar(30), Leave INT)
INSERT INTO #finalData Values('Test1', 5),('Test2', 10),('Test3', 2)

DECLARE @cols AS NVARCHAR(MAX),  
    @query  AS NVARCHAR(MAX);  

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ColName)   
            FROM #finalData c  
            FOR XML PATH(''), TYPE  
            ).value('.', 'NVARCHAR(MAX)')   
        ,1,1,'')  

set @query = 'SELECT ' + @cols + ' from   
            (  
                select ColName  
                    , Leave  
                from #finalData  
           ) x  
            pivot   
            (  
                 max(Leave)  
                for ColName in (' + @cols + ')  
            ) p '  

execute(@query)  
DROP TABLE #finalData

Hope this will help you. The output is as shown below

Test1   Test2   Test3
5       10      2
user_105325000 
select pv.* from
(SELECT Name,Leave
FROM tblUser INNER JOIN tblLeaveMaster ON tblUser.EmployeeID=tblLeaveMaster.EmployeeID
)X
pivot
(max(leave) for name in ([Test1],[Test2],[Test3])) as pv

Note:- this work for me but how to use where condition base on dropdown list selected value. eg: if wants to show only 2020 and not 2019.

user_144973890
Related