一 行转列
1多行转成一列(并以","隔开)
SELECT name ,
value = ( STUFF(( SELECT ',' + value
FROM A
WHERE name = Test.name
FOR
XML PATH('')
), 1, 1, '') )
FROM A AS Test
GROUP BY name;
PS:STUFF语句就是为了去掉第一个【逗号】
附STUFF用法:(从原字符的第二个开始共三个字符替换为后面的字符)
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
2 SQL Server 2000静态SQL通过group by 、max语句进行行转列静态SQL
select a.MemberId,a.Age,a.Gender,a.ColorectalCancerFamilyHistory,a.ProstateCancerFamilyHistory,
max(CASE a.Conidtion WHEN 'Colorectal Cancer' THEN a.RiskScore ELSE '' end) ColorectalCancerRiskScore,
max(CASE a.Conidtion WHEN 'Colorectal Cancer' THEN a.RiskLevel ELSE '' end) ColorectalCancerRiskLevel,
max(CASE a.Conidtion WHEN 'Prostate Cancer' THEN a.RiskScore ELSE '' end) ProstateCancerIPSSScore,
max(CASE a.Conidtion WHEN 'Prostate Cancer' THEN a.RiskLevel ELSE '' end) ProstateCancerRiskLevel,
max(CASE a.Conidtion WHEN 'Diabetes Mellitus' THEN a.RiskLevel ELSE '' end) DiabetesMellitusRiskLevel,
max(CASE a.Conidtion WHEN 'Breast Cancer' THEN a.RiskScore ELSE '' end) BreastCancerAbsoluteRisk,
max(CASE a.Conidtion WHEN