Group by Rows in to columns

[rrchowdhary]

Hi experts

I am struggling to get my query right, as currently I am getting the following output, but what I am trying for is to get all my data in to the single rows.
Could any one please shed some light and point me in the right direction.

Many Thanks

Country	ID	Company	QuestionID	Buildings(300)	Machinery(301)	AverageStock(302)	ThirdParty(303)
Mexico	E627	Mexico 	300		27000000	NULL		NULL			NULL
Mexico	E627	Mexico 	300		7200000		NULL		NULL			NULL
Mexico	E627	Mexico 	301		NULL		119000000	NULL			NULL
Mexico	E627	Mexico 	301		NULL		221000000	NULL			NULL
Mexico	E627	Mexico 	302		NULL		NULL		1,200,000		NULL	
Mexico	E627	Mexico 	302		NULL		NULL		1,300,000		NULL			
Mexico	E627	Mexico 	303		NULL		NULL		NULL			4300000
Mexico	E627	Mexico 	303		NULL		NULL		NULL			NULL			


	DECLARE @QID1 int
	DECLARE @QID2 int
	DECLARE @QID3 int
	DECLARE @QID4 int

	SET @QID1=300
	SET @QID2=301
	SET @QID3=302
	SET @QID4=303

	SELECT DISTINCT
		cc.company as [Country]
		,qa_Buildings.Value [Buildings(300)]
		,qa_machinery.Value [Machinery(301)]
		,qa_Averagestock.Value [AverageStock(302)]
		,qa_ThirdParty.Value [ThirdParty(303)]
	FROM [fn_get_company_hierarchy](105,1,1) ch 

	INNER JOIN  tb_ctrl_companies		cc			ON ch.CompanyId=cc.CompanyId
	INNER JOIN  tb_qaire_submission_key	qsk			ON cc.CompanyId = ISNULL(qsk.CompanyID,0) 
	INNER JOIN tb_qaire_submissions		sub			ON sub.SubmissionKeyID = qsk.KeyID AND sub.QuestionnaireID = 15
	INNER JOIN tb_qaire_answers		qa			ON qsk.KeyID = qa.SubmissionKeyID 
	INNER JOIN tb_qaire_questions		qq			ON qq.QuestionID=qa.QuestionID
	
	

	LEFT JOIN tb_qaire_answers qa_Buildings 
		ON qa.QuestionId=qa_Buildings.QuestionID 
		AND qa.SubmissionKeyID=qa_Buildings.SubmissionKeyID 
		AND qa_Buildings.QuestionId=@QID1

	LEFT JOIN tb_qaire_answers qa_machinery 
		ON qa.QuestionId=qa_machinery.QuestionID 
		AND qa.SubmissionKeyID=qa_machinery.SubmissionKeyID 
		AND qa_machinery.QuestionId=@QID2

	LEFT JOIN tb_qaire_answers qa_Averagestock  
		ON qa.QuestionId=qa_Averagestock.QuestionID 
		AND qa.SubmissionKeyID=qa_Averagestock.SubmissionKeyID 
		AND qa_Averagestock.QuestionId=@QID3

	LEFT JOIN tb_qaire_answers qa_ThirdParty  
		ON qa.QuestionId=qa_ThirdParty.QuestionID 
		AND qa.SubmissionKeyID=qa_ThirdParty.SubmissionKeyID 
		AND qa_ThirdParty.QuestionId=@QID4

	WHERE 
		qq.QuestionId IN(295,296,297,298,300,301,302,303,304) 
		AND (qsk.PeriodYear=2009 OR qsk.PeriodYear=2008)
		AND  qsk.PeriodMonth = 1

 

[markros]

If you want a single row, you need to group by
Country ID Company QuestionID and take a sum of each value. Use your current select as derived table, e.g.

select Country,    ID,    Company,    QuestionID,
sum([Buildings(300)]) as [Buildings(300)], ...
from (my current select) X 
group by Country,    ID,    Company,    QuestionID

PluralSight Learning Library

 

[rrchowdhary]

Hi markros

Thanks for your reply, but I am not looking for a group by solution, as I just need to get the data in the following format

Country    ID    Company    QuestionID    Buildings(300)    Machinery(301)    AverageStock(302)    ThirdParty(303)
Mexico    E627    Mexico     300        27000000    		119000000        1,200,000            4300000
Mexico    E627    Mexico     300        7200000        		221000000         1,300,000            NULL

 

[r937]

if this data is retrieved by an application language like ASP or C or whatever, then you should be doing the "crosstab" layout there, and not with SQL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

I see, you were joining to itself multiple times. I think you needed to use PIVOT instead.

PluralSight Learning Library