Click here to Skip to main content
15,891,033 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello all, I have a sql table with basic employee details as below

table: tblEmployees
EmpID     Name       Contact     Sex
100       John       55555        M
200       Kate       44444        F
300       Sam        88888        M


I would like to get my query result as follows of a particular employee where EmpID=200

Col1         Col2
EmpID        200
Name         Kate
Sex          F


What I have tried:

i have tried playing with pivot and unpivot , but filed to achieve the same. Any help will be highly appreciated.
T
Posted
Updated 11-Sep-20 0:04am
v3

declare @table table ( id int identity(1,1), EMPID varchar(5)  , [Name] varchar(20), contact Varchar(20) , gender char(10))

insert into @table ( empid,  [name], contact, gender) values ('100','John',       '55555',        'M')
insert into @table ( empid,   [name], contact, gender) values ('200','Kate',       '44444',        'F')
insert into @table ( empid,    [name], contact, gender) values ('300','Sam',       '44444',        'M')


declare @kvpTable table (keyVal varchar(20), val varchar(20) , objectKey int )


declare @min int = 0, @max int = 0

select @min = min(id)  , @max = max(id) from @table 
declare @name varchar(20) 
declare @contact varchar(20)
declare @mf char(1)
declare @emp varchar(8)

while @min <= @max
begin
select @emp= empid,  @name = [name], @contact = contact , @mf = gender  from @table where id = @min 

insert into @kvpTable 
select 'EmpID', @emp , @min
union
select 'name', @name, @min
union
select 'contact', @contact, @min
union
select 'gender' ,@mf , @min

set @min = @min + 1
end

select * from @kvpTable
 
Share this answer
 
Comments
CHill60 11-Sep-20 6:14am    
Rarely, very rarely, do you ever need to use a loop in set based languages such as SQL
UNPIVOT is what you need.
Try:
SQL
SELECT Col2, Col1 FROM (SELECT * FROM tblEmployees WHERE Empid = 200) a
UNPIVOT(Col1 FOR Col2 IN (EmpID, Name, Sex)) AS unp
 
Share this answer
 
Comments
Viswanatha Swamy 8-Mar-20 4:50am    
Do we need to cast if the columns are of multi-data types? Example: Employee Id is int and the rest of the columns are of varchar type.
OriginalGriff 8-Mar-20 4:58am    
Yes. Think about it: it is returning a table, which is a set of rows and columns. A column can only contain data of a single type so if you want to "mix and match" you need to cast to ensure they are the same.
Replace "*" in the SELECT list with the columns you want, and use CAST to convert them - you will need to name the "new" columns or it will rightly complain!
Viswanatha Swamy 8-Mar-20 8:00am    
Good evening. I had a table with a Unique Identifier, Varchar, int, and DateTime. I have used the below command for the output. I have tested in SQL Server Mgt Studio.

SELECT [ColumnName], [ColumnValue]
FROM Health
CROSS APPLY
(
VALUES
('Id', cast(Id as varchar(50))),
('Name', [Name]),
('Health Parameter1', cast(HealthParameter1 as varchar(10))),
('Health Parameter2', cast(HealthParameter2 as varchar(10))),
('Received At', cast(ReceivedAt as varchar(50)))
) c ([ColumnName], [ColumnValue])
where Id = 'F84BBA87-6241-4E0A-4F42-08D7C291F501';
Maciej Los 8-Mar-20 9:10am    
5ed!
SulfySul 9-Mar-20 1:27am    
Yes, this answered my question.

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