改变表维度 [英] change table dimension
本文介绍了改变表维度的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
任何人都可以帮我构建这个查询:以下是输入&输出
输入表格:
Empno PhoneType Number
100 Mobile 1234
100电话3456
101手机5678
102电话7890
103手机3412
输出:
< pre lang =text> Empno移动电话
100 1234 3456
101 5678 -
102 - 7890
103 3412 -
解决方案
试试这个:
DECLARE @ PhoneDtls TABLE (Empno INT ,PhoneType VARCHAR ( 20 ),Number VARCHAR ( 20 ))
INSERT INTO @ PhoneDtls (EmpNo,PhoneType,Number)
SELECT 100 ,' Mobile',' 1234'
UNION ALL SELECT 100 ,' Telephone',' 3456'
UNION ALL SELECT 101 ,' Mobile',' 5678'
UNION ALL SELECT < span class =code-digit> 102 ,' Telephone',' 7890'
UNION 所有 SELECT 103 ,' Mobile',' 3412'
SELECT EmpNo,PhoneType,Number FROM @ PhoneDtls
SELECT Empno,ISNULL([Mobile],' - ')' Mobile',ISNULL([电话], ' - ')' Telepho ne'
FROM (
SELECT *
FROM @ PhoneDtls
) AS DT
PIVOT(MAX([Number]) FOR [PhoneType] IN ([Mobile ],[电话])) AS PT
更多:使用PIVOT和UNPIVOT [ ^ ]
您好,
请尝试以下代码...
DECLARE @ PhoneDtls TABLE (Empno INT ,PhoneType VARCHAR ( 20 ),Number VARCHAR ( 20 ))
INSERT INTO @ PhoneDtls (EmpNo,PhoneType,Number)
SELECT 100 ,' Mobile',' 1234'
UNION 所有 SELECT 100 ,' 电话',' 3456'
UNION ALL SELECT 101 ,' Mobile',' 5678'
UNION ALL SELECT 102 , ' Telephone',' 7890'
UNION ALL SELECT 103 ,' Mobile', ' 3412'
SELECT EmpNo,PhoneType,Number FROM @ PhoneDtls
选择 T.EmpNo,
ISNULL(( SELECT Number FROM @ PhoneDtls WHERE PhoneType = ' 移动' 和 EmpNo = T.EmpNo),' - ')' Mobile',
ISNULL( ( SELECT 编号 FROM @ PhoneDtls WHERE PhoneType = ' Telephone' AND EmpNo = T.EmpNo),' - ') ' Number'
F ROM ( SELECT DISTINCT EmpNo FROM @ PhoneDtls )T
问候,
GVPrabu
dear sir/ma'am
can anybody help me in framing this query :Following are the input & output
Input table:
Empno PhoneType Number
100 Mobile 1234
100 Telephone 3456
101 Mobile 5678
102 Telephone 7890
103 Mobile 3412
Output:
Empno Mobile Telephone
100 1234 3456
101 5678 -
102 - 7890
103 3412 -
解决方案
Try this:
DECLARE @PhoneDtls TABLE (Empno INT, PhoneType VARCHAR(20), Number VARCHAR(20)) INSERT INTO @PhoneDtls(EmpNo,PhoneType,Number) SELECT 100,'Mobile','1234' UNION ALL SELECT 100,'Telephone','3456' UNION ALL SELECT 101,'Mobile','5678' UNION ALL SELECT 102,'Telephone','7890' UNION ALL SELECT 103,'Mobile','3412' SELECT EmpNo,PhoneType,Number FROM @PhoneDtls SELECT Empno, ISNULL([Mobile],'-') 'Mobile', ISNULL([Telephone] ,'-') 'Telephone' FROM ( SELECT * FROM @PhoneDtls ) AS DT PIVOT(MAX([Number]) FOR [PhoneType] IN([Mobile], [Telephone])) AS PT
More: Using PIVOT and UNPIVOT[^]
Hi,
Try the below Code...
DECLARE @PhoneDtls TABLE (Empno INT, PhoneType VARCHAR(20), Number VARCHAR(20)) INSERT INTO @PhoneDtls(EmpNo,PhoneType,Number) SELECT 100,'Mobile','1234' UNION ALL SELECT 100,'Telephone','3456' UNION ALL SELECT 101,'Mobile','5678' UNION ALL SELECT 102,'Telephone','7890' UNION ALL SELECT 103,'Mobile','3412' SELECT EmpNo,PhoneType,Number FROM @PhoneDtls SELECT T.EmpNo, ISNULL((SELECT Number FROM @PhoneDtls WHERE PhoneType ='Mobile' AND EmpNo=T.EmpNo),'-') 'Mobile', ISNULL((SELECT Number FROM @PhoneDtls WHERE PhoneType ='Telephone' AND EmpNo=T.EmpNo),'-') 'Number' FROM (SELECT DISTINCT EmpNo FROM @PhoneDtls) T
Regards,
GVPrabu
这篇关于改变表维度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文