CODEWITHSUNDEEP
sqlsql-servert-sqlpivotunpivot
Sulfy
Sulfy

Reputation: 227

Get column names and data to rows in SQL

I have a 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

Upvotes: 0

Views: 532

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81990

Another option is with a little XML

Full Disclosure: Not as performant as GMB's CROSS APPLY (+1) or UNPIVOT. BUT it will dynamically unpivot virtually any row, table, view or ad-hoc query without actually using dynamic SQL.

Example

Declare @YourTable Table ([EmpID] varchar(50),[Name] varchar(50),[Contact] varchar(50),[Sex] varchar(50))  Insert Into @YourTable Values 
 (100,'John',55555,'M')
,(200,'Kate',44444,'F')
,(300,'Sam',88888,'M')

 Select A.EmpID
       ,C.*
 From @YourTable A
 Cross Apply ( values (convert(xml,(select a.* for XML Raw ))) ) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)') not in ('EmpID','Other','Columns2Exclude')
             ) C

Returns

EmpID   Item    Value
100     Name    John
100     Contact 55555
100     Sex     M
200     Name    Kate
200     Contact 44444
200     Sex     F
300     Name    Sam
300     Contact 88888
300     Sex     M

EDIT - If Interested Here a TVF approach

Select A.EmpID
      ,B.*
 From  @YourTable A
 Cross Apply [dbo].[tvf-XML-UnPivot-Row]((Select A.* for XML RAW)) B

The TVF

CREATE FUNCTION [dbo].[tvf-XML-UnPivot-Row](@XML xml)
Returns Table 
As
Return ( 
        Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
              ,Value = xAttr.value('.','varchar(max)')
         From  @XML.nodes('//@*') xNode(xAttr)
)

Upvotes: 0

Kate
Kate

Reputation: 1836

Or a less sophisticated solution involving 3 UNIONs, assuming the field names are predetermined in advance. This might perform better on large tables. If you have performance issues, analyze the execution plan and make sure indexes are utilized optimally.

Since you are only looking for one particular employee at a time:

SELECT 'empid', convert(varchar(12), EmpID)
FROM tblEmployees
WHERE EmpID = 200
UNION ALL
SELECT 'name', name
FROM tblEmployees
WHERE EmpID = 200
UNION ALL
SELECT 'sex', sex
FROM tblEmployees
WHERE EmpID = 200

The first line does convert(varchar(12) under the assumption that EmpID is an int field.

Upvotes: 0

GMB
GMB

Reputation: 222582

You can use cross apply:

select t.*
from employees e
cross apply (values 
    ('empid', cast(empid as varchar(100))),
    ('name', name), 
    ('sex', sex)
) t(attr, value)
where e.empid = 200

Presumably, empid is a number, so explicit casting is needed (otherwise sql server will try to cast the name and sex to numbers, which will fail).

Demo on DB Fiddle:

attr  | value
:---- | :----
empid | 200  
name  | Kate 
sex   | F

Upvotes: 5

Related Questions