SQLSERVER行转列列转行

原创已于 2022-07-11 16:47:46 修改 · 6.2k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql

于 2021-03-01 16:41:40 首次发布

sqlserver 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用SQL将逗号分隔的系统编号转换为实际系统名称的方法，通过行转列和列转行的技术实现数据的高效转换。

场景:
2个表
一个存储逗号分隔得系统编号现在需要将系统编号转换为实际系统名称
在这里插入图片描述
1.行转列
将项目表的ApplySys 拆分为多条数据
SELECT ApplySys , * FROM p_project
SELECT * FROM dbo.myApplication

–行转列

SELECT ProjGuid,ApplySys,c.ApplicationName FROM (
SELECT  A.ProjGuid, B.ApplySys
	FROM(
	SELECT p_projectId ProjGuid,
	ApplySys=CONVERT(XML,'<v>' + REPLACE(ApplySys, ',', '</v><v>') + '</v>')
	FROM p_project
)AS A
OUTER APPLY(
	SELECT ApplySys=N.a.value('.', 'varchar(100)') FROM A.ApplySys.nodes('/v') N(a) 
)AS B
WHERE ISNULL(b.ApplySys,'')!=''
) T
LEFT JOIN myApplication c ON c.Application=t.ApplySys

在这里插入图片描述
2.列转行将列数据转换为一行根据项目显示对应的系统


```sql
SELECT  
    ProjGuid,  
    ApplicationName = (  
               STUFF(  
                    (SELECT ',' + ApplicationName  
                     FROM temp  
                     WHERE ProjGuid = A.ProjGuid  
                     FOR xml path('')  
                    ),1,1,''  
                    )  
                 )  
FROM temp A  
GROUP by ProjGuid




3.完整代码如下

```sql
```csharp
WITH temp AS (
SELECT distinct ProjGuid,ApplySys,c.ApplicationName FROM (
SELECT  A.ProjGuid, B.ApplySys
	FROM(
	SELECT p_projectId ProjGuid,
	ApplySys=CONVERT(XML,'<v>' + REPLACE(ApplySys, ',', '</v><v>') + '</v>')
	FROM p_project
)AS A
OUTER APPLY(
	SELECT ApplySys=N.a.value('.', 'varchar(100)') FROM A.ApplySys.nodes('/v') N(a) 
)AS B
WHERE ISNULL(b.ApplySys,'')!=''
) T
LEFT JOIN myApplication c ON c.Application=t.ApplySys
)
SELECT  
    ProjGuid,  
    ApplicationName = (  
               STUFF(  
                    (SELECT ',' + ApplicationName  
                     FROM temp  
                     WHERE ProjGuid = A.ProjGuid  
                     FOR xml path('')  
                    ),1,1,''  
                    )  
                 )  
FROM temp A  
GROUP by ProjGuid