Merging Multiple Row Into 1 Row

Question

Hello everyone, I have a table with the description as shown below: But, I want to display the result like this: Can someone teach me how to do it? thanks in advance

Vishal Joshi · Answer

Hello, Please try the below optimized query to get same result.it will make only single call to table. we can use SUM and CASE . SELECT SUM(CASE when a.Q3_IGA_pig '' THEN 1 ELSE 0 END) as Q3_IGA_pig, SUM(CASE when a.Q3_IGA_goat '' THEN 1 ELSE 0 END) as Q3_IGA_goat, SUM(CASE when a.Q3_IGA_poultry '' THEN 1 ELSE 0 END) as Q3_IGA_poultry, SUM(CASE when a.Q3_IGA_Fish '' THEN 1 ELSE 0 END) as Q3_IGA_Fish, SUM(CASE when a.Q3_IGA_Maize '' THEN 1 ELSE 0 END) as Q3_IGA_Maize, SUM(CASE when a.Q3_IGA_Cassava '' THEN 1 ELSE 0 END) as Q3_IGA_Cassava, SUM(CASE when a.Q3_IGA_Weaning '' THEN 1 ELSE 0 END) as Q3_IGA_Weaning, SUM(CASE when a.Q3_IGA_Others '' THEN 1 ELSE 0 END) as Q3_IGA_Others FROM [DBO].[LMFA_PRF_IGA] AS a Thanks

Jignesh Kumar · Answer

Hello Hold On, You can use Pivot to acheve this one, https://www.c-sharpcorner.com/UploadFile/suthish_nair/convert-rows-to-columns-in-sql-server-20082005/ https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server

Manikandan R · Answer

Hello Small Try, SELECT (Select COUNT(a.Q3_IGA_pig) FROM [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_pig )as Q3_IGA_pig, (Select COUNT(a.Q3_IGA_goat) FROM [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_goat )as Q3_IGA_goat, . . . . . Your Statements..

Jes Sie · Answer

Hi Naimish, thanks for your reply. Your solution will give me a vertical result. What I wanted is a horizontal result. So, below is what I did select COUNT(a.Q3_IGA_pig) as Q3_IGA_pig, (select COUNT(a.Q3_IGA_goat) as Q3_IGA_goat from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_goat '') as Q3_IGA_goat, (select COUNT(a.Q3_IGA_poultry) as Q3_IGA_poultry from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_poultry '') as Q3_IGA_poultry, (select COUNT(a.Q3_IGA_Fish) as Q3_IGA_Fish from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_Fish '') as Q3_IGA_Fish, (select COUNT(a.Q3_IGA_Maize) as Q3_IGA_Maize from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_Maize '') as Q3_IGA_Maize, (select COUNT(a.Q3_IGA_Cassava) as Q3_IGA_Cassava from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_Cassava '') as Q3_IGA_Cassava, (select COUNT(a.Q3_IGA_Weaning) as Q3_IGA_Weaning from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_Weaning '') as Q3_IGA_Weaning, (select COUNT(a.Q3_IGA_Others) as Q3_IGA_Others from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_Others '') as Q3_IGA_Others from [dbo].[LMFA_PRF_IGA] a where a.Q3_IGA_pig '' The above-mentioned query gives me the result that I wanted. However, if there is a simpler way to do it, I need to try.

Naimish Makwana · Answer

Hello, You can use UNION operator. Example : SELECT count(a.Q3_IGA_pig) AS Count FROM LMFA WHERE Q3_IGA_pig '' UNION SELECT count(a.Q3_IGA_Goat) AS Count FROM LMFA WHERE Q3_IGA_Goat '' UNION SELECT count(a.Q3_IGA_Fish) AS Count FROM LMFA WHERE Q3_IGA_Fish '' ----- Your other select statment. Thanks