Click here to Skip to main content
14,300,872 members
Rate this:
Please Sign up or sign in to vote.
See more:
Company_ID	Customer_ID	Status
1	        3453453	      S
1	        3488453	      E
1	        3453453	      G
2	        4453453	      S
3	        3489453	      S
2	        1345453	      E
3	        3487453	      S
3	        3453453	      S
3	        3454553	      S


Output like

Company_ID	G	S	E
1	        1	1	1
2	        0	1	1
3	        0	4	0


What I have tried:

I tried lot of way in a pivot table but I could reach my solutions
Posted
Updated 11-Sep-19 2:23am
Comments
Patrice T 11-Sep-19 8:26am
   
Then, show the codes you tried.
Rate this:
Please Sign up or sign in to vote.

Solution 1

If you "tried a lot of way" then you will have code: so show it to us and explain what it did that you didn't expect, or didn't do that you did.

Alternatively, go here: PIVOT and UNPIVOT in Sql Server | SqlHints.com[^] and start reading.
   
Comments
CPallini 11-Sep-19 9:23am
   
5.
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try this:

SELECT Company_ID, [G], [S], [E]
FROM (
    SELECT Company_ID, Customer_ID, [Status]
    FROM YourTable
) DT
PIVOT(COUNT(Customer_ID) FOR [Status] IN ([G], [S], [E])) PT
   
Comments
CPallini 11-Sep-19 9:23am
   
5.
Maciej Los 11-Sep-19 9:36am
   
Thank you, Carlo.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100