sql server 横向转丛向及FOR XML PATH使用

---横向转丛向
select 
    name ‘姓名‘,
    max(case when course=‘语文‘ then score end) as ‘语文‘,
    max(case when course=‘数学‘ then score end) as ‘数学‘,
    max(case when course=‘物理‘ then score end) as ‘物理‘
from
t_score
group by name

select stuff(‘ world‘,1,0,‘hello‘) --hello world

select *from t_score for xml path

<row>
  <id>1</id>
  <name>张三</name>
  <course>语文</course>
  <score>73</score>
</row>
<row>
  <id>2</id>
  <name>张三</name>
  <course>数学</course>
  <score>83</score>
</row>
<row>
  <id>3</id>
  <name>张三</name>
  <course>物理</course>
  <score>93</score>
</row>
<row>
  <id>4</id>
  <name>李四</name>
  <course>语文</course>
  <score>74</score>
</row>
<row>
  <id>5</id>
  <name>李四</name>
  <course>数学</course>
  <score>84</score>
</row>
<row>
  <id>6</id>
  <name>李四</name>
  <course>物理</course>
  <score>94</score>
</row>

select *from t_score for xml path(‘Mygrades‘)

<Mygrades>
  <id>1</id>
  <name>张三</name>
  <course>语文</course>
  <score>73</score>
</Mygrades>
<Mygrades>
  <id>2</id>
  <name>张三</name>
  <course>数学</course>
  <score>83</score>
</Mygrades>
<Mygrades>
  <id>3</id>
  <name>张三</name>
  <course>物理</course>
  <score>93</score>
</Mygrades>
<Mygrades>
  <id>4</id>
  <name>李四</name>
  <course>语文</course>
  <score>74</score>
</Mygrades>
<Mygrades>
  <id>5</id>
  <name>李四</name>
  <course>数学</course>
  <score>84</score>
</Mygrades>
<Mygrades>
  <id>6</id>
  <name>李四</name>
  <course>物理</course>
  <score>94</score>
</Mygrades>

select 
    name,
    course,
    score
from t_score for xml path(‘Mygrades‘)

<Mygrades>
  <name>张三</name>
  <course>语文</course>
  <score>73</score>
</Mygrades>
<Mygrades>
  <name>张三</name>
  <course>数学</course>
  <score>83</score>
</Mygrades>
<Mygrades>
  <name>张三</name>
  <course>物理</course>
  <score>93</score>
</Mygrades>
<Mygrades>
  <name>李四</name>
  <course>语文</course>
  <score>74</score>
</Mygrades>
<Mygrades>
  <name>李四</name>
  <course>数学</course>
  <score>84</score>
</Mygrades>
<Mygrades>
  <name>李四</name>
  <course>物理</course>
  <score>94</score>
</Mygrades>

select
    course+‘;‘
from t_score where name=‘张三‘ for xml path(‘‘)
--结果：语文;数学;物理;

select
    ‘[‘+course+‘]‘
from t_score where name=‘张三‘ for xml path(‘‘)
--结果：[语文][数学][物理];

select
    ‘{‘+course+‘}‘
from t_score where name=‘张三‘ for xml path(‘‘)
--结果：{语文}{数学}{物理};

select 
    name ‘姓名‘,
    stuff((SELECT ‘;‘+course FROM t_score where name=‘张三‘ FOR XML PATH(‘‘)),1,1,‘‘) ‘科目‘
from 
t_score
group by name