CODEWITHSUNDEEP
t-sqlpivot
user3231451
user3231451

Reputation: 23

Tsql denormalize a table with text values

I have a table like

UserID      Attribute      Value
1           Name           Peter
1           Sex            male
1           Nationality    UK
2           Name           Sue

And need a result like

UserId     Name    Sex   Nationality .....
1          Peter   male  Uk
2          Sue .....

Looks like crosstab - in MS Access that works if I take First(Value) - in T SQL I cannot get it to work with Value being a text field

Any ideas?

Upvotes: 2

Views: 414

Answers (1)

M.Ali
M.Ali

Reputation: 69564

DECLARE @TABLE TABLE (UserID INT, Attribute VARCHAR(20),Value VARCHAR(20))
INSERT INTO @TABLE VALUES
(1,'Name','Peter'),
(1,'Sex','male'),
(1,'Nationality','UK'),
(2,'Name','Sue')


SELECT * FROM @TABLE
PIVOT ( MAX(Value)
        FOR Attribute
        IN ([Name], [Sex], [Nationality]))P

Result Set

╔════════╦═══════╦══════╦═════════════╗
║ UserID ║ Name  ║ Sex  ║ Nationality ║
╠════════╬═══════╬══════╬═════════════╣
║      1 ║ Peter ║ male ║ UK          ║
║      2 ║ Sue   ║ NULL ║ NULL        ║
╚════════╩═══════╩══════╩═════════════╝

Upvotes: 2

Related Questions