CODEWITHSUNDEEP
sqlsql-server
Kalirajan
Kalirajan

Reputation: 42

Convert Table to Specific Column Wise

I have a table like this. How can I convert to this format?

DECLARE @A TaBLE (KeyValue INT, Name VARCHAR(50), Value VARCHAR(512))

INSERT INTO @A 
VALUES (0, 'AccountID', '192507'), (0, 'member_id', '999159'),
       (0, 'firstname', 'Test1'), (0, 'lastname', 'Last1'),
       (1, 'AccountID', '192508'), (1, 'member_id', '999160'),
       (1, 'firstname', 'Test2'), (1, 'lastname', 'Last2')

SELECT * FROM @A

I have table rows for this model:

KeyValue     Name           Value
-----------------------------------
0          AccountID        192507
0          member_id        999159
0          firstname        Test1
0          lastname         Last1
1          AccountID        192508
1          member_id        999160
1          firstname        Test2
1          lastname         Last2

My expected output is:

AccountID   member_id   firstname   lastname
--------------------------------------------
192507       999159       Test1      Last1
192508       999160       Test2      Last2

I tried this code But it didn't work 

select *
from
(
  select Name,value
  from @A
) d
pivot
(
  MAX(value)
  for Name in (AccountID,member_id,firstname,lastname)
) piv;

Upvotes: 0

Views: 55

Answers (3)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

In the temp table, you should select all useful information like this

select AccountID, member_id, firstname, lastname
from
(
  select * from @A -- instead of `select Name,value`
) d
pivot
(
  MAX(value)
  for Name in (AccountID,member_id,firstname,lastname)
) piv;

Result here

enter image description here

Upvotes: 1

iVad
iVad

Reputation: 573

You can get the desired result by using PIVOT. In your query you just need to select all the columns, like below.

SELECT AccountID, member_id, firstname, lastname
FROM
(
  select * from @A
) d
PIVOT
(
  MAX(value)
  FOR Name IN (AccountID, member_id, firstname, lastname)
) piv;

You can run the test here.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Try this below logic-

DEMO HERE

SELECT 
MAX(CASE WHEN Name = 'AccountID' THEN Value ELSE NULL END) AccountID,
MAX(CASE WHEN Name = 'member_id' THEN Value ELSE NULL END) member_id ,
MAX(CASE WHEN Name = 'firstname' THEN Value ELSE NULL END) firstname ,
MAX(CASE WHEN Name = 'lastname' THEN Value ELSE NULL END) lastname 
FROM @A
GROUP BY KeyValue

Upvotes: 1

Related Questions