博客

场景:

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