Click here to Skip to main content
15,301,401 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have Employee table it contains 4 columns Emp_Id,Emp_Name,Allowance_Names,And Allowance values,

I need an out put like this

SQL
Emp_Id  Emp_Name Allowance_Name1  Allowance_Name2.....   
-----   -------- ---------------  ---------------
   101   AAA     Allowancevalues    Allowancevalues....


Can anyone know please help me to do it..
Posted

Here[^] you'll find a tons of examples.
   
Check my article for Pivot details with example query
I have made sample for you.

SQL
-- Create Table
Create Table EmpDetails
(
Emp_Id  int,
Emp_Name varchar(40),
Allowance_Names varchar(40),
Allowance_Values varchar(40),
)
--Sample data Insert
Insert into EmpDetails Values(1,'Emp1',     'AllowNames1',         'Values1')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames2',         'Values2')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames3',         'Values3')
Insert into EmpDetails Values(1,'Emp1',     'AllowNames4',         'Values4')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames1',         'Values2_1')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames2',         'Values2_2')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames3',         'Values2_3')
Insert into EmpDetails Values(2,'Emp2',     'AllowNames4',         'Values2_4')


-- Pivot Select Query
DECLARE @MyColumns AS NVARCHAR(MAX),
    @SQLquery  AS NVARCHAR(MAX)
-- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Allowance_Names) 
                    FROM EmpDetails
                    GROUP BY Allowance_Names
                    ORDER BY Allowance_Names
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @SQLquery = N'SELECT Emp_Id,Emp_Name,' + @MyColumns + N' from 
             (
               SELECT 
                Emp_Id,
				Emp_Name, 
				Allowance_Values as values2 
				,Allowance_Names
    FROM EmpDetails
            ) x
            pivot 
            (
                 max(values2)
                for Allowance_Names in (' + @MyColumns + N')
            ) p '
exec sp_executesql @SQLquery;

for more details check my articles.
Basic SQL Queries for Beginners[^]
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900