CODEWITHSUNDEEP
sql-server
QKWS
QKWS

Reputation: 1109

Convert rows to columns in MS SQL

I'm looking for an efficient way to convert rows to columns in MS SQL server.

Example DB Table:

**ID    PersonID    Person201Code   Person201Value**
  1     1           CurrentIdNo     0556
  2     1           FirstName       Queency
  3     1           LastName        Sablan

The query result should be like this:

**CurrentIdNo     FirstName         LastName**
  0556            Queency             Sablan

I tried using PIVOT but it only return null on row values:

SELECT CurrentIdNo, FirstName, LastName
FROM

(
  SELECT ID, PersonId, Person201Code, Person201Value
  FROM HRPerson201
) src
PIVOT
(
  MAX (ID)
  FOR Person201Code in (CurrentIdNo, Firstname, LastName))
 pvt;

How can I successfully convert rows to columns in MS SQL server? Thanks!

Upvotes: 4

Views: 276

Answers (2)

Chanukya
Chanukya

Reputation: 5893

  SELECT *
FROM
  (SELECT personid,Person201Code,Person201Value
   FROM #pivot) Sales
  PIVOT(max(Person201Value)
   FOR Person201Code in (CurrentIdNo, Firstname, LastName))
  AS PivotSales;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Remove the ID from pivot source query and add Person201Value pivot aggregate instead of ID

SELECT CurrentIdNo,
       FirstName,
       LastName
FROM   (SELECT PersonId,
               Person201Code,
               Person201Value
        FROM   HRPerson201) src
       PIVOT ( Max (Person201Value)
             FOR Person201Code IN (CurrentIdNo,
                                   Firstname,
                                   LastName)) pvt;

Upvotes: 3

Related Questions