问题描述
我有一个表格,其中包含如下所示的数据。
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
推荐答案
;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
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服务器查询将行作为列获取,但保持列不变的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!