sql 多行、一行互转（逗号分割）

最新推荐文章于 2024-07-18 17:04:58 发布

踏雪无痕

最新推荐文章于 2024-07-18 17:04:58 发布

阅读量2.8k

点赞数 1

分类专栏： SQL Server 2000/2005/2008

原文链接：https://www.cnblogs.com/xiao-sheng/p/9573295.html

版权

SQL Server 2000/2005/2008 专栏收录该内容

210 篇文章 4 订阅

订阅专栏

原始数据：

期望数据：

IF OBJECT_ID('temp_20170701','u') IS NOT NULL DROP TABLE temp_20170701

 CREATE TABLE temp_20170701 (
 ID INT PRIMARY KEY IDENTITY(1,1),
 NAME  NVARCHAR(50),
 Subjectname NVARCHAR(50),
 Score INT
 )

INSERT dbo.temp_20170701( Name, subjectname, Score )
SELECT 'A','语文','20' UNION
SELECT 'A','数学','30' UNION
SELECT 'A','英语','40' UNION
SELECT 'B','语文','50' UNION
SELECT 'B','数学','60' UNION
SELECT 'B','英语','70' UNION
SELECT 'C','语文','80' UNION
SELECT 'C','数学','90' UNION
SELECT 'C','英语','100' UNION
SELECT 'D','英语','100'

SELECT Name ,Score=STUFF((SELECT ','+CONVERT(NVARCHAR(max),Score) FROM temp_20170701 t1 WHERE t1.NAME=t2.NAME FOR XML PATH('')),1,1,'')
 FROM temp_20170701 t2 GROUP BY t2.NAME

原始数据：

期望数据：

CREATE TABLE temp_20170702 (
ID INT PRIMARY KEY IDENTITY(1,1),
NAME NVARCHAR(50),
Score varchar(100)
)

insert temp_20170702 (Name,Score)
select 'A','30,40,20' union
select 'B','60,70,50' union
select 'C','90,100,80' union
select 'D','100'

select a.NAME,b.value as Score from (
select *,s=CONVERT(xml,'<root><v>'+REPLACE(Score,',','</v><v>')+'</v></root>') from temp_20170702 
 ) a outer apply 
(select value=n.s.value('.','varchar(100)') from a.s.nodes('/root/v') n(s)) b