SQL SERVER中的纵横查询

关于sql的纵横查询，其实说白了就是pivot(行转列)、unpivot(列转行)、字符拼接、for xml path。

网上写关于这方面的东西太少，大多都是把他们分开写的，所以我给大家汇个总

给大家来几个例子，应该能弄明白吧！

首先创建个表插几条数据吧

<span style="font-size:14px;">create table dbo.test
    (
      id int ,
      name nvarchar(20) ,
      score int
    )
insert  into kktest.dbo.test
        select  1 ,
                '张三' ,
                60
        union
        select  1 ,
                '李四' ,
                65
        union
        select  1 ,
                '王麻子' ,
                70
        union
        select  1 ,
                '大麻子' ,
                75

</span>

select * from test

id name score
1  大麻子 75
1  李四     65
1  王麻子 70
1  张三     60

pivot

<span style="font-size:14px;">select  * 
from dbo.test pivot
(
 sum([score]) for [name]
 in
 ([张三],[李四],[王麻子],[大麻子])
)as s</span>

结果：

id  张三  李四  王麻子  大麻子
1  60      65      70         75

这里简单的说下这个函数吧，后面的别名还是一定要的，在pivot中的列一般养成个好习惯加[]，对于

int类型的列来说，执行会报这列无效的错误，因为是行转列，所以，除你做对象的列其他的列如果不能

聚合，那么查询出来的列中没有数据的地方会用null填充。

unpivot

首先来个错误的

<span style="font-size:14px;">SELECT * FROM dbo.test 
unpivot (值 for 列 in([id],[name],[score])) as s 
where name = '王麻子'</span>

结果：

列 "name" 的类型与 UNPIVOT 列表中指定的其他列的类型冲突

所以说要转的类型必须是要是同类型！

再来个正确的别打脸

<span style="font-size:14px;">SELECT * FROM dbo.test 
unpivot (值 for 列 in([id],[score])) as s 
where name = '王麻子'</span>

结果：

name 值 列
王麻子 1 id
王麻子 70 score

这里只查一行就是为了图个对比。。。

然后就是行拼接，差不多就是："***"+"***"

国际惯例，先来个错的：

<span style="font-size:14px;">select id+name+score
from dbo.test</span>

结果：

在将 nvarchar 值 '大麻子' 转换成数据类型 int 时失败。

新手应该能够理解吧！

再来个：

<span style="font-size:14px;">select cast(id as nvarchar(50))+name+cast(score as nvarchar(50))  as 合并
from dbo.test</span>

结果：

合并
1大麻子75
1李四65
1王麻子70
1张三60

这里呢为什么要转成nvarchar类型？因为字符拼接要转成字符你打我我报警

不服来个int不转字符直接拼：

<span style="font-size:14px;">select id+score
from dbo.test</span>

结果：

(无列名)
76
66
71
61

结果是相加的

for xml path

这里来两种方式吧，结果都是为了把列拼起来

<span style="font-size:14px;">select stuff(( select  ',' + ( isnull(name, '') )
        from    test
for     xml path('') ),
           1,1,'') as xml拼</span>

结果：

xml拼
大麻子,李四,王麻子,张三

<span style="font-size:14px;">select id, name=stuff((select ','+name 
from kktest.dbo.test as b where a.id=b.id for xml path('')),1,1,'')
from kktest.dbo.test as a
group by a.id</span>

结果：

id name
1 大麻子,李四,王麻子,张三

这里就是把表转成xml格式，至于为什么要用isnull就是如果有一行是null那么整个串都是null。

行列的玩法有很多种！大家自己摸索吧