SQL服务器查询将行作为列获取，但保持列不变 [英] SQL server query to get rows as columns but keeping the columns as is

问题描述

我有一个表格，其中包含如下所示的数据。



FieldID DocumentID ClassID PreValue PreScore PreStatus PostValue PostScore PostValueChanged



1 123 1约翰80.00约翰80.00 N



2 123 1 123456 71.23 N 1234 0.00 Y



1 124 2 Tom 95.65 Y Tom 95.65 N



2 124 2 5689 53.26 N 5689 53.26 Y



有人可以帮助SQL以下列方式获取数据： -



DocumentID ClassID 1- PreValue 1-PreScore 1-PreStatus 1-PostValue 1-PostScore 1-PostValueChanged 2-PreValue 2-PreScore 2-PreStatus 2-PostValue 2-PostScore 2-PostValueChanged



123 1 John 80 Y John 80 N 123456 71.23 N 1234 0 Y



124 2 Tom 95.65 Y Tom 95.65 N 5689 53.26 N 5689 53.26 Y



我尝试使用Pivot条款，但无法得到它。请帮忙。



谢谢，Inder

I have a table which contains data as shown below.

FieldID DocumentID ClassID PreValue PreScore PreStatus PostValue PostScore PostValueChanged

1 123 1 John 80.00 Y John 80.00 N

2 123 1 123456 71.23 N 1234 0.00 Y

1 124 2 Tom 95.65 Y Tom 95.65 N

2 124 2 5689 53.26 N 5689 53.26 Y

Could someone please help with a SQL to get data in following fashion:-

DocumentID ClassID 1-PreValue 1-PreScore 1-PreStatus 1-PostValue 1-PostScore 1-PostValueChanged 2-PreValue 2-PreScore 2-PreStatus 2-PostValue 2-PostScore 2-PostValueChanged

123 1 John 80 Y John 80 N 123456 71.23 N 1234 0 Y

124 2 Tom 95.65 Y Tom 95.65 N 5689 53.26 N 5689 53.26 Y

I tried using the Pivot Clause but couldn't get it. Please help.

thanks, Inder

推荐答案

你可以这样做：

我们称你的桌子文件



所以

you can do something like this:
we call your table document

so

;with document_n as (
select * from document 
where PostValueChanged='N'),
 document_y as (
select PreValue,PreScore,PreStatus,PostValue,PostScore,PostValueChanged from document 
where PostValueChanged='Y'),

select * from document_n inner join 
              document_y on   document_n.document_id= document_y.document_id and                                                                                                                       document_n.classid= document_y.classid

我能够为PreValue列实现这个...但是无法为5列的其余部分做到这一点...所以如何连接其余的10个colmns即2 * 5。

这里是片段。 。

I was able to achieve this for PreValue column... but couldn't make it for rest of the 5 columns... so struggling with how to concatenate rest 10 colmns i.e. 2*5.
Here's the snippet..

Declare @colPivot varchar(Max)
Select @colPivot = STUFF((Select Distinct TOP 100 Percent ',' + quotename(CONVERT(VARCHAR(255),RTRIM(LTRIM(t2.FieldID))) + 'PreValue')
				From [dbo].[tbldocument] as t2 Inner join [dbo].[tbldocument] as t3 on t3.DocumentID=t2.DocumentID 
				where t2.BatchClassID=t3.BatchClassID
				Order by ',' + quotename(CONVERT(VARCHAR(255),RTRIM(LTRIM(t2.FieldID))) + 'PreValue')
				For xml path(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')


Print @colPivot




Declare @query varchar(Max)

Set @query = 'Select * From (Select		
							[DocumentID],
							[ClassID],
							[PreValue],
							convert(varchar(255),[FieldID]) + ''PreValue'' as Fieldwise
							
							From 
								[TestDB].[dbo].[tbldocument]
							)A
			Pivot(max([PreValue]) For Fieldwise	 IN ('+ @colPivot +'))p'
			
PRINT (@query)
Exec (@query)

这篇关于SQL服务器查询将行作为列获取，但保持列不变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！