query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-04-03 : 19:09:49

I need a query...

tablea:
FID Bigint primarykey
Sname varchar
TID1 tinyint
CID1 tinyint
TID2 tinyint
CID2 tinyint
TID3 Tinyint
CID3 tinyint


FID	Sname	Tid1   CID1   TID2   CID2   TID3  CID3
----	-----	----   ----   ----   ----   ----  ----
1       HT      3      10      2      4      2     5
2       NT      2      11                    3     5
3       PT                     3      5


I want to display data like below:

COndition:When there is data in TID1 & CID1 then i want the display a 1
When there is data in TID2 & CID2 then i want the display a 2
When there is data in TID3 & CID3 then i want the display a 3

When there is no data in TID1 & CID1 and there is data in TID2 & CID2 then I want to display 1. So on.



FID	Sname	Tid1   CID1     TID2   CID2   TID3  CID3
----	-----  	----   ----     ----   ----   ----  ----
1       HT    1  3      10  2    2      4   3    2     5
2       NT    1  2      11                  2    3     5
3       PT                   1    3     5


Please let me know if i am not clear....

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-04 : 06:56:37

try this,

Declare @t Table (FID Bigint, Sname varchar(100), TID1 tinyint, CID1 tinyint, TID2 tinyint,
CID2 tinyint, TID3 Tinyint, CID3 tinyint)

Insert Into @t
Select 1, 'HT', 3, 10, 2, 4, 2, 5 Union All
Select 2, 'NT', 2, 11, Null, Null, 3, 5 Union All
Select 3, 'PT', Null, Null, 3, 5, Null, Null

Select FID, Sname,
TID1, CID1,
Case When TID1 is not null and CID1 is not null Then 1 End As '1',
TID2, CID2,
Case When (TID1 is null and CID1 is null) and (TID2 is not null and CID2 is not null) Then 1
When TID2 is not null and CID2 is not null Then 2
End As '2',
TID3, CID3,
Case When (TID1 is null and CID1 is null) and (TID2 is null and CID2 is null) and (TID3 is not null and CID3 is not null) Then 1
When (TID1 is null and CID1 is null) and (TID2 is not null and CID2 is not null) and (TID3 is not null and CID3 is not null) Then 2
When (TID1 is not null and CID1 is not null) and (TID2 is null and CID2 is null) and (TID3 is not null and CID3 is not null) Then 2
When TID3 is not null and CID3 is not null Then 3
End As '3'
From @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources