how to get * records from tb1 and resume in tb2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get * records from tb1 and resume in tb2

Author

Topic

GnR_Slash
Starting Member

14 Posts

Posted - 2010-05-12 : 07:40:50

Hi guys, i need to build a storedprocedure on SQL Server 2005 to return the results to a C#.NET 2008 program

I have 2 tables: tb1 and tb2 like this:

tb1:
UID name
1 John
2 Eduardo

tb2:
UID ID Status
1 1 YELLOW
2 1 YELLOW
3 1 RED
4 2 BLUE
4 2 RED

I need this result:

ID Name YELLOW RED BLUE
1 John yes yes no
2 Eduardo no yes yes

I need to know if each person on tb1 has that 3 colors added on tb2

note: the yes/no results may be just a count of records on tb2

Is this possible with a query or I will need to codify SQL?

thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 07:54:03

thats easy. do like


SELECT tb1.UID,tb1.Name,
MAX(CASE WHEN tb2.Status='YELLOW' THEN 'Yes' ELSE 'No' END) AS YELLOW,
MAX(CASE WHEN tb2.Status='RED' THEN 'Yes' ELSE 'No' END) AS RED,
MAX(CASE WHEN tb2.Status='BLUE' THEN 'Yes' ELSE 'No' END) AS BLUE
FROM tb1
JOIN tab2
ON tb2.ID=tb1.UID
GROUP BY tb1.UID,tb1.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/