水平转换表值 [英] Convert table values Horizontally

问题描述

HI朋友，



我在下表中遇到问题，

HI Friends,

I have a problem in the following table,

1	555	pass	yes	NULL	NULL
2	555	pass	no	NULL	NULL
3	555	NULL	no	NULL	NULL
4	555	NULL	no	NULL	NULL
5	555	fail	no	NULL	NULL
6	555	NULL	no	NULL	NULL
7	555	pass	no	NULL	NULL
8	555	NULL	no	NULL	NULL

我想将所有垂直记录转换为水平，即

1,555，pass，yes，NULL，NULL，2,555，Pass，no，NULL，NULL，3 ，555，NULL，no，NULL，NULL ...



但是这里我没有使用aggrigate函数，所以我不能使用pivot。 />


请建议我如何在sql server中解决这个问题。



谢谢

I want to convert all the vertical records in to the horizontal i.e.
1 ,555 ,pass ,yes ,NULL ,NULL ,2 ,555 ,Pass ,no ,NULL ,NULL ,3 ,555 ,NULL ,no ,NULL ,NULL...

but here i am not using the aggrigate function so that i can''t use pivot.

please suggest me how to do this to solve this in sql server.

Thanks

推荐答案

让我用你的值创建一个示例表。

Let me create a sample table with your values.

SELECT
*
INTO MyTable
FROM
(
SELECT 
1 AS SL,	555 AS ID,	'pass' AS [Status],	'yes' AS Qualified, NULL AS ST1,	NULL AS ST2
UNION ALL
SELECT 
2	,555,	'pass', 	'no'	, NULL, 	NULL
UNION ALL
SELECT 
3	,555,	NULL,	'no',	NULL	, NULL
UNION ALL
SELECT 
4	,555,	NULL, 	'no'	, NULL, 	NULL
UNION ALL
SELECT 
5	,555,	'fail',	'no'	, NULL, 	NULL
UNION ALL
SELECT 
6	,555,	NULL, 	'no'	, NULL, 	NULL
UNION ALL
SELECT 
7	,555,	'pass', 	'no'	, NULL, NULL
UNION ALL
SELECT 
8	,555,	NULL, 	'no'	, NULL, NULL
)X

您需要的查询在这里：

And the query you need is here:

SELECT SUBSTRING(MyRow,0,LEN(MyRow)) AS MyRow FROM
(
	SELECT DISTINCT ID,
	(
		SELECT CONVERT(VARCHAR(10),SL)+ ',' 
		     + CONVERT(VARCHAR(10),ID)+ ',' 
		     + CONVERT(VARCHAR(10),COALESCE([Status],''))+ ','  
		     + CONVERT(VARCHAR(10),COALESCE(Qualified,''))+ ','
		     + CONVERT(VARCHAR(10),COALESCE(ST1,''))+ ','
		     + CONVERT(VARCHAR(10),COALESCE(ST2,''))+ ','
		     AS [text()] 
		FROM dbo.MyTable AS TBL_A WHERE TBL_A.ID=TBL_B.ID
		FOR XML PATH('')	
	) AS MyRow
 	FROM dbo.MyTable AS  TBL_B
)
AS TBL_C

结果

The result

MyRow
1,555,pass,yes,0,0,2,555,pass,no,0,0,3,555,,no,0,0,4,555,,no,0,0,5,555,fail,no,0,0,6,555,,no,0,0,7,555,pass,no,0,0,8,555,,no,0,0

SELECT SUBSTRING(C,1,LEN(C)-1)'MyRow' FROM (SELECT 1'A',(SELECT CONVERT(VARCHAR(10),SL)+ ','
             + CONVERT(VARCHAR(10),ID)+ ','
             + CONVERT(VARCHAR(10),COALESCE([Status],''))+ ','
             + CONVERT(VARCHAR(10),COALESCE(Qualified,''))+ ','
             + CONVERT(VARCHAR(10),COALESCE(ST1,''))+ ','
             + CONVERT(VARCHAR(10),COALESCE(ST2,''))+ ','
             AS [text()]
        FROM dbo.MyTable AS TBL_A
        FOR XML PATH(''))AS C)T

这篇关于水平转换表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！