SQL Query Cross Table

Question

I hope I explain this right and I do not want to make this a store procedure

This query does work for me, but I want it to do something else. I want to modified it to do something else

I have table that changes every other day. I need one column from that table and the data from it. This column can have 200 – 350 rows in it every other day when it changes, and some of the values will be the same so I will need to group them together

I do not want to insert the value in it each day by hand so I am wondering what can I change in that so I do not need to do that
And
I do not want to add in the sum by hand in it either

This way when it changes every other day don’t need to do anything

Here is the query I have

Declare @testTable Table (Eggs int); --I do not want to add in the values by hand
Insert Into @testTable (Eggs)
Values (1), (1), (2), (2), (5), (3), (2), (4), (2), (4), (3), (2), (1);

Select *
From @testTable tt;

--==== Solution
Select egg1 = sum(Case When tt.Eggs = 1 Then 1 Else 0 End) -- I do not want to add sum by hand
, egg2 = sum(Case When tt.Eggs = 2 Then 1 Else 0 End)
, egg3 = sum(Case When tt.Eggs = 3 Then 1 Else 0 End)
, egg4 = sum(Case When tt.Eggs = 4 Then 1 Else 0 End)
, egg5 = sum(Case When tt.Eggs = 5 Then 1 Else 0 End)
From @testTable tt;

The out put would look like some like this

Egg1 | egg2 | egg3| egg4 | egg5

1 3 | 5 | 2 | 2 | 1

Answer 1

What is the source table you want to use? You can swap out the @testTable that I used for this example with your actual table - and reference the actual column names from that source table.

This was provided as an example to your question - now you need to incorporate the solution into your actual tables and query.

Answer 2

Hi @Tim Roscoe ,

Welcome to Microsoft Q&A!

You could have a try to fulfill this requirement with dynamic SQL as below:

--DDL and insert sample data  
create table testTable (Eggs int)  
Insert Into testTable (Eggs)  
Values (1), (1), (2), (2), (5), (3), (2), (4), (2), (4), (3), (2), (1), (6);  

declare @n int   
declare @max int  
set @n=1  
select @max= max(eggs) from testTable  
  
declare @sql nvarchar(max)  
set @sql='Select '  
  
while @n<=@max  
begin  
  select @sql=@sql+'sum(case when tt.Eggs = '+cast(@n as char(2))+' then 1 else 0 end) egg'+cast(@n as char(2))+','  
  set @n=@n+1  
end  
set @sql=SUBSTRING(@sql,1,len(@sql)-1)  
set @sql=@sql+' From testTable tt'  
  
EXECUTE sp_executesql @sql  

Output:

egg1	egg2	egg3	egg4	egg5	egg6  
3	5	2	2	1	1  

After all, you could refer above example and update your statement according to your actual requirement.

If you still have any issue or concern , you could post a new question together with some sample data of your actual data and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.