MS-SQL SERVER学习笔记（一）一种很实用的行转列思路

首先是先看到了这样一个问题，关于动态行转列的，name和val成对出现，数量不定，现要求按照每个cid一行，每个cid所拥有的全部name和val设置为该行的列。我看了一下，感觉因为name和val有成对的关系，要一起行转列，pivot一次只能把一行转成一列，没办法直接使用pivot，得想别的办法。

然而大神一的做法：我就是要pivot，我弄两张表，表一按顺序存name（列名为name1，name2，name3，name4…），表二按顺序存val（列名为val1，val2，val3，val4…），然后select表一的时候，在每一列后面加一个空列，列名为val（select null as val），把select出的表一变成（name1，val1，name2，val2，name3，val3…），表二也这么弄，然后再union all并起来，不就可以了嘛
结果会变成

cid	name1	val1	name2	val2	name3	val3	name4	val4
1	xx	空	xx	空	xx	空	xx	空
2	xx	空	xx	空	xx	空	xx	空
3	xx	空	xx	空	xx	空	xx	空
1	空	xx	空	xx	空	xx	空	xx
2	空	xx	空	xx	空	xx	空	xx
3	空	xx	空	xx	空	xx	空	xx

只要group by cid然后分别对name和val取max就行了

SELECT      cid, MAX(N1) N1, MAX(V1) v1, MAX(N2) n2, MAX(V2) v2, MAX(N3) n3, MAX(V3) v3, MAX(N4) n4, MAX(V4) v4
FROM        (   SELECT  cid, p.[1] N1, NULL AS V1, p.[1] AS N2, NULL AS V2, p.[3] AS N3, NULL AS V3, p.[4] AS N4, NULL AS V4
                FROM    (SELECT cid, [name], xh FROM #ta) a
                PIVOT (   MAX(name)
                          FOR xh IN ([1], [2], [3], [4])) p
                UNION ALL
                SELECT  cid, NULL AS N1, p.[1] AS V1, NULL AS N2, p.[2] AS V2, NULL AS N3, p.[3] AS V3, NULL AS N4, p.[4] AS V4
                FROM    (SELECT cid, [val], xh FROM #ta) a
                PIVOT (   MAX(val)
                          FOR xh IN ([1], [2], [3], [4])) p) t
                          GROUP BY cid

虽然大神一的方法完美的解决了题主的问题，但是我还是总感觉缺点什么——如果name和val再多一组呢，岂不是又要修改sql了？所以pivot还是不能用的。
然而当时的我已经陷入了误区，我只觉得这肯定没办法用pivot了，所以我觉得，我想出了一个很靠谱的思路：我先按对每个cid来select它的name和val，每次只select一行，然后join起来，这样就可以搞出很多很多的表，然后只要再把这些表union all起来，不就妥了【只要？不就？】？我真是个天er才bi！
原表

cid	name	val
1	n1	v1
1	n2	v2
2	n3	v3
2	n4	v4
3	n5	v5

第一步变成三张表，这也太简单了吧 我select name,val where cid = 1查出来，然后各行join起来不就行了？

cid	name	val	name	val
1	n1	v1	n2	v2

cid	name	val	name	val
2	n3	v3	n4	v4

cid	name	val
3	n5	v5

第二步就是union起来就行了，这还不简单？【那时候我还没有发现这里隐藏着一个问题，神了】
思路清晰简洁又好实现，这题已经解决95%了
说干就干，先一个cid一个cid的把它的name和val给弄出来

好，我先搞一个字符串变量用来存储sql语句

declare @sql varchar(max) ='',@cid varchar(20)
---这里必须要有=''，要不一会儿拼接sql的时候null+任何东西=null就舒服了

然后就是把cid=@cid的所有name和val给它join起来，只需要一个简单的sql拼接就行了

select @sql = 
@sql+' left join (select '''+ name +''' name,'+cast(val as varchar(20))
+' val)' + QUOTENAME(name)+'on 1=1' from #TA where cid=@cid

好！那么现在出现了一个不算问题的问题，怎么取每个cid来一个一个的执行上面的sql呢？这个问题也不复杂，只要我定义一个变量i，和一个变量max，令max=cid的个数，每次往下取一个cid，循环max次就好了

while(@i<=@max)----第i个不同的CID
begin
select top 1 @cid=cid from #TA 
where cid not in 
(select top (@i-1) cid from #TA group by cid) 
group by cid  
set @i = @i + 1
end

现在我已经能select出了数量为cid个数的表了，他们的形式长成这样

name1	val1	name2	val2	name3	val3	…

但是这样是不行的（你以为我发现问题了？不我没有），还要再在前面加一个cid然后就可以union all了（吗？答案当然是否定的）。
这个也很简单，只要提前给@sql赋一句查询cid的sql字符串值就行了

set @sql = 'union all select * from (select cid from #TA where cid='''
+ @cid +''' group by cid) a'

轻松啊，以上的代码组合起来

declare @sql varchar(max) ='',@cid varchar(20)----这一段是声明变量

select @max=MAX(a.rownum) from 
(select ROW_NUMBER() over(order by cid) rownum from #TA group by cid) a
---上面查总共有多少cid，
---我为什么不select count(1) cid group by cid或者select distinct呢，很神奇
while(@i<=@max)----这一段是一个一个的选出cid循环
begin
select top 1 @cid=cid from #TA 
where cid not in 
(select top (@i-1) cid from #TA group by cid) 
group by cid  

set @sql = 'union all select * from (select cid from #TA where cid='''
+ @cid +''' group by cid) a'----这一段是加上第一列cid还有union all的命令

select @sql = ---这一段是把cid里所有的name和val给拼（join）成一行
@sql+' left join (select '''+ name +''' name,'+cast(val as varchar(20))
+' val)' + QUOTENAME(name)+'on 1=1' from #TA where cid=@cid

set @i = @i + 1
end

然后就可以

set @sql = SUBSTRING(@sql,11,8000)
exec(@sql)

了吗？
不行，会报错，（实际上我是先试的@sql没写union all的情况，能把那些表都分别查出来，我还想着只要加个union all不就跟按下火箭发射的按钮一样简单吗？现在想想都这个时候了还没发现问题，我也是挺厉害的），因为各行的长度不一样。哦我终于发现问题了，原来第二步没这么简单啊，不是union all一下就行的。
嗨，白高兴一场。不过解决也好解决，加空列呗（left join (select null as name,null as val) ），我只要把要union的每张表（其实就是每行）都加到和最长的那一行一样长就可以union了。

select @maxcol = MAX(a1.cols) from 
(select a.cid,COUNT(*) cols from #TA a group by a.cid) a1
---神了，我当时是怎么想的，不过反正找到最长的行的列数了

--在之前的while循环里加上另一个循环
while(@cols<@maxcol)
begin
set @sql1 = 
@sql1 + 
' left join (select NULL name,Null val) addnullcolumn'+ 
cast(@cols as varchar(5)) +' on 1=1'---join的东西要有不能重复的别名，我还得给它搞个别名
set @cols = @cols +1
end

这下好像就可以成功了，以下是完整的代码

declare @sql varchar(max)='',@sql1 varchar(1000),@cid varchar(50),@max int,@i int = 1,
@cols int,@maxcol int
---查一共有多少不同的CID
select @maxcol = MAX(a1.cols) from (select a.cid,COUNT(*) cols from #TA a group by a.cid) a1
select @max=MAX(a.rownum) from (select ROW_NUMBER() over(order by cid) rownum from #TA group by cid) a
while(@i<=@max)----第i个不同的CID
begin
select top 1 @cid=cid from #TA where cid not in (select top (@i-1) cid from #TA group by cid) group by cid  
select @cols = a1.cols from(select COUNT(*) cols from #TA a where a.cid = @cid) a1
set @sql1 = 'union all select * from (select cid from #TA where cid='''+ @cid +''' group by cid) a'
select @sql1 =
       @sql1 + 
       ' left join (select '''+ name +''' name,'+cast(val as varchar(20))+' val)' + 
       QUOTENAME(name)+'on 1=1' from #TA where cid=@cid
while(@cols<@maxcol)
begin
set @sql1 = @sql1 + ' left join (select NULL name,Null val) addnullcolumn'+ cast(@cols as varchar(5)) +' on 1=1'
set @cols = @cols +1
end
set @sql = @sql + @sql1 ---这个@sql是干什么的，为什么我要分两个sql字符串
set @i = @i + 1
end
set @sql = SUBSTRING(@sql,11,8000)
print(@sql)
exec(@sql)

总算是实现了功能，然而，大佬出现了：

DECLARE @sql VARCHAR(max)=''
SELECT 
@sql=@sql
+',max(case when xh='+CONVERT(VARCHAR(10),xh)+' then name else null end) as name'+CONVERT(VARCHAR(10),xh)
+',max(case when xh='+CONVERT(VARCHAR(10),xh)+' then val else null end) as val'+CONVERT(VARCHAR(10),xh) +CHAR(13)
FROM 
(
	SELECT xh FROM  #TA GROUP BY xh
) a 

SET @sql='select cid'+@sql+' from #TA group by cid'
EXEC(@sql)

DROP TABLE #TA

一眼就能看懂什么意思，就不赘述了，而且在@sql中只select了一次，算上拼接sql字符串也只select了两次而已，效率极高。拿到这个思路我如获至宝，直接套用解决了另一个类似的问题（话说大佬也回答了那个问题，不过用的是不同的方法，圣斗士从不用同一招击败对手？），不过那是另一个故事了。

事故现场https://bbs.csdn.net/topics/398467884

另一个故事https://bbs.csdn.net/topics/397546986