sql server 怎么把多行数据按tjbh 整合成一行数据

转换成

CREATE TABLE #T
(
	id INT IDENTITY,
	typ VARCHAR(10),
	txt VARCHAR(10),
	val text 
)

INSERT INTO #T(typ,txt,val) VALUES(1,'a','1'),(1,'b','2'),(1,'c','3'),(1,'d','4'),(1,'e','5'),(1,'f','6')
INSERT INTO #T(typ,txt,val) VALUES(2,'a','1'),(2,'b','2'),(2,'c','3'),(3,'e','5'),(3,'f','6')

 
--去掉id,在子查询里把类型转换一下
SELECT *  FROM 
(
SELECT typ,txt,CONVERT(VARCHAR(max),val) AS val  FROM #T 
) a
PIVOT
(
	max(val) 
	FOR txt IN (a,b,c,d,e,f)
)b

DROP TABLE #T

需要考虑一列有多个值的情况吗

你这个结果是行列转换来的吧,转换的时候多了一个key字段吧,你在转换前,把多余的字段去掉

--例子
CREATE TABLE #T
(
	id INT IDENTITY,
	typ VARCHAR(10),
	txt VARCHAR(10),
	val INT 
)

INSERT INTO #T(typ,txt,val) VALUES(1,'a',1),(1,'b',2),(1,'c',3),(1,'d',4),(1,'e',5),(1,'f',6)
INSERT INTO #T(typ,txt,val) VALUES(2,'a',1),(2,'b',2),(2,'c',3),(3,'e',5),(3,'f',6)

--全部
SELECT *  FROM #T 
PIVOT
(
	SUM(val) 
	FOR txt IN (a,b,c,d,e,f)
)b

--去掉id
SELECT *  FROM 
(
SELECT typ,txt,val  FROM #T 
) a
PIVOT
(
	SUM(val) 
	FOR txt IN (a,b,c,d,e,f)
)b

DROP TABLE #T