Merging Multiple Row Into 1 Row

Jes Sie

3y969SQL Server

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi3y ago

Accepted 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 Kumar3y ago
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 R3y ago

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 Sie3y ago

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 Makwana3y ago

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