我有一个场景,其中我有三列多行,我需要在单行中选择它们。下面我附上临时表方案。
CREATE TABLE #Temp
(
PersonID INT ,
PhoneType VARCHAR(20) ,
PhoneNumber VARCHAR(20)
)
INSERT INTO #Temp
( PersonID ,
PhoneType ,
PhoneNumber
)
SELECT 1212 ,
'Business' ,
'123456789'
UNION ALL
SELECT 1212 ,
'Cell' ,
'741852963'
UNION ALL
SELECT 1212 ,
'Other' ,
'987654321'
UNION ALL
SELECT 1212 ,
'Home' ,
'951357852'
SELECT *
FROM #Temp
DROP TABLE #Temp
我需要展示
PersonID|Business|123456789|Cell|741852963|Other|987654321|Home|951357852
有人可以帮忙吗?
我必须将此结果与另一个选择查询结合起来,该怎么办?
您可以使用条件聚合:
SELECT
PersonID,
Business = MAX(CASE WHEN PhoneType = 'Business' THEN PhoneNumber END),
Cell = MAX(CASE WHEN PhoneType = 'Cell' THEN PhoneNumber END),
Other = MAX(CASE WHEN PhoneType = 'Other' THEN PhoneNumber END),
Home = MAX(CASE WHEN PhoneType = 'Home' THEN PhoneNumber END)
FROM #Temp
GROUP BY PersonID
如果您有未知数量PhoneType
的,请执行动态交叉表:
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql =
'SELECT
PersonID' + CHAR(10)
SELECT @sql = @sql +
' , MAX(CASE WHEN PhoneType = ''' + PhoneType + ''' THEN PhoneNumber END) AS' + QUOTENAME(PhoneType) + CHAR(10)
FROM (SELECT DISTINCT PhoneType FROM #Temp) t
SELECT @sql = @sql +
'FROM #Temp
GROUP BY PersonID'
EXEC sp_executesql @sql
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句