SQL行转列

SELECT ProjectID ,
value = ( STUFF(( SELECT ',' + convert(varchar,ReturnRatio)
FROM tb_PublicReturn
WHERE ProjectID = Test.ProjectID
FOR
XML PATH('')
), 1, 1, '') )
FROM tb_PublicReturn AS Test where Test.ProjectID = 126
GROUP BY ProjectID;

 

select
(select top 1 r.SupportAmount from tb_PublicReturn r where r.ProjectID = P.PP_ID) as SupportAmount,
(SELECT
value = ( STUFF(( SELECT ',' + convert(varchar,ReturnRatio)
FROM tb_PublicReturn
WHERE ProjectID = Test.ProjectID
FOR
XML PATH('')
), 1, 1, '') )
FROM tb_PublicReturn AS Test where Test.ProjectID = P.PP_ID
GROUP BY ProjectID
) as ReturnRatio,
EntryName,ProjectaaType,ReleaseTime,
(select Sum(Total) from tb_PublicOrder where ProjectID=P.PP_ID and Extra1 in (1,2,3)) as Total,
(select COUNT( DISTINCT PlatformID) from tb_PublicOrder where ProjectID=P.PP_ID and Extra1 in (1,2,3)) as TotalPeople, (select DISTINCT UserID from tb_PublicOrder where ProjectID=P.PP_ID and Extra1 in (1,2,3)) as UserID, PublicFinancing,FinancingAmoun,[Day],P.AddTime,(Extra3)as Profit

from
tb_PublicProject P
where 1=1

and (select Sum(Total) from tb_PublicOrder where ProjectID=P.PP_ID and Extra1 in (1,2,3) and Userid=28) >0
and P.Type=2