如何使用SQL将行数据作为列进行拍摄 [英] How can I shot Row Data as columns using SQL

问题描述

我有两张这样的表（由另一个程序使用，因此无法更改结构）

Hi,
I have two tables like this (Used by another program, so cant change the structure)

Table Name: User

UserId       UserName        DisplayName
-----------------------------------------
1            AAA             AAA
2            BBB             BBB
3            CCC             CCC

Table Name: User Profile

UserId       Property        Value
-----------------------------------------
1            Age             21
1            Address         Address of AAA
1            Email           [e-mail] 
2            Age             25
2            Address         Address of BBB
2            Email           [e-mail] 
3            Age             28
3            Address         Address of CCC
3            Email           [e-mail] 

我想要的是获得Ta像这样使用查询

What I want is to get the Table as like this using a query

UserId      UserName     DisplayName     Age    Address             Email
-----------------------------------------------------------------------------------
1           AAA          AAA             21     Address of AAA     [e-mail]
2           BBB          BBB             25     Address of BBB     [e-mail]
3           CCC          CCC             28     Address of CCC     [e-mail]

任何人都可以请帮助我？



谢谢

Can anybody please help me ?

Thanks

推荐答案

SELECT u.UserId, UserName, DisplayName,
(SELECT u2.Value FROM [User Profile] u2 WHERE u2.Property="Age" AND u2.UserId = u.UserId) AS Age,
(SELECT u3.Value FROM [User Profile] u3 WHERE u3.Property="Address" AND u3.UserId = u.UserId) AS Address,
(SELECT u4.Value FROM [User Profile] u4 WHERE u4.Property="Email" AND u4.UserId = u.UserId) AS Email
FROM User u

尝试：

Try:

SELECT u.[UserId], u.[UserName], u.[DisplayName], pa.Value as Age, pd.Value as Address, pe.Value as Email FROM [User] u
JOIN UserProfile pa ON pa.UserId=u.USerId AND pa.Property='Age'
JOIN UserProfile pd ON pd.UserId=u.USerId AND pd.Property='Address'
JOIN UserProfile pe ON pe.UserId=u.USerId AND pe.Property='Email'

这篇关于如何使用SQL将行数据作为列进行拍摄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！