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-07 : 20:58:11

I want a query which should generate a sequence based on the value exists in the columns, #.

there are 7 columns to be checked TID1... TID7.
When there is value exists in TID1 then ID1 Should have value 1
When there is value exists in TID2 then ID2 Should have value 2
When there is value exists in TID3 then ID3 Should have value 3
When there is value exists in TID4 then ID4 Should have value 4
When there is value exists in TID5 then ID5 Should have value 5
When there is value exists in TID6 then ID6 Should have value 6
When there is value exists in TID7 then ID7 Should have value 7

When there is null in TID1 then ID1 Should have value null
When there is value exists in TID2 then ID2 Should have value 1
When there is value exists in TID3 then ID3 Should have value 2
When there is value exists in TID4 then ID4 Should have value 3
When there is value exists in TID5 then ID5 Should have value 4
When there is value exists in TID6 then ID6 Should have value 5
When there is value exists in TID7 then ID7 Should have value 6

so on........

Please let me know if m not clear...



Declare @t Table (FID Bigint, Sname varchar(100), TID1 tinyint, TID2 tinyint, TID3 tinyint,
TID4 tinyint, TID5 Tinyint, TID6 tinyint, TID7 tinyint)



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

Select * from @t

FID                  Sname      TID1 TID2 TID3 TID4 TID5 TID6 TID7
-------------------- ---------- ---- ---- ---- ---- ---- ---- ----
1                    HT         3    10   2    4    2    5    9
2                    NT         2    11   NULL NULL 3    5    8
3                    PT         NULL NULL 3    5    NULL NULL 7






Desired Output:

FID                  Sname      ID1  TID1 ID2  TID2 ID3  TID3 Id4  TID4 Id5  TID5 Id6  TID6 Id7  TID7
-------------------- ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1                    HT         1    3    2    10   3    2    4    4    5    2    6    5    7    9
2                    NT         1    2    2    11   NULL NULL NULL NULL 3    3    4    5    5    8
3                    PT         NULL NULL NULL NULL 1    3    2    5    NULL NULL NULL NULL 3    7

Thanks for the help in advance !!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-07 : 22:18:18

[code]
select
FID,
Sname,
case WHEN TID1 is null then NULL else 1 end as ID1, -- The easy one
TID1, -- the easy one
case
when TID2 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end )
END as ID2,
TID2,
case
when TID3 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end ) + (case WHEN TID3 is null then 0 else 1 end )
END as ID3,
TID3,
case
when TID4 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end ) + (case WHEN TID3 is null then 0 else 1 end ) + (case WHEN TID4 is null then 0 else 1 end )
END as ID4,
TID4,
case
when TID5 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end ) + (case WHEN TID3 is null then 0 else 1 end ) + (case WHEN TID4 is null then 0 else 1 end )+ (case WHEN TID5 is null then 0 else 1 end )
END as ID5,
TID5,
case
when TID6 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end ) + (case WHEN TID3 is null then 0 else 1 end ) + (case WHEN TID4 is null then 0 else 1 end )+ (case WHEN TID5 is null then 0 else 1 end )+ (case WHEN TID6 is null then 0 else 1 end )
END as ID6,
TID6,
case
when TID7 is null
then NULL
else (case WHEN TID1 is null then 0 else 1 end ) + (case WHEN TID2 is null then 0 else 1 end ) + (case WHEN TID3 is null then 0 else 1 end ) + (case WHEN TID4 is null then 0 else 1 end )+ (case WHEN TID5 is null then 0 else 1 end )+ (case WHEN TID6 is null then 0 else 1 end )+ (case WHEN TID7 is null then 0 else 1 end )
END as ID7,
TID7
from @t

[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-04-07 : 23:11:14

Thanks it works...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 02:47:30

[code]SELECT e.fID AS FID,
e.sName AS Sname,
MAX(CASE WHEN e.tID = 'tID1' THEN e.ID ELSE NULL END) AS ID1,
MAX(CASE WHEN e.tID = 'tID1' THEN e.idValue ELSE NULL END) AS TID1,
MAX(CASE WHEN e.tID = 'tID2' THEN e.ID ELSE NULL END) AS ID2,
MAX(CASE WHEN e.tID = 'tID2' THEN e.idValue ELSE NULL END) AS TID2,
MAX(CASE WHEN e.tID = 'tID3' THEN e.ID ELSE NULL END) AS ID3,
MAX(CASE WHEN e.tID = 'tID3' THEN e.idValue ELSE NULL END) AS TID3,
MAX(CASE WHEN e.tID = 'tID4' THEN e.ID ELSE NULL END) AS ID4,
MAX(CASE WHEN e.tID = 'tID4' THEN e.idValue ELSE NULL END) AS TID4,
MAX(CASE WHEN e.tID = 'tID5' THEN e.ID ELSE NULL END) AS ID5,
MAX(CASE WHEN e.tID = 'tID5' THEN e.idValue ELSE NULL END) AS TID5,
MAX(CASE WHEN e.tID = 'tID6' THEN e.ID ELSE NULL END) AS ID6,
MAX(CASE WHEN e.tID = 'tID6' THEN e.idValue ELSE NULL END) AS TID6,
MAX(CASE WHEN e.tID = 'tID7' THEN e.ID ELSE NULL END) AS ID7,
MAX(CASE WHEN e.tID = 'tID7' THEN e.idValue ELSE NULL END) AS TID7
FROM (
SELECT p.fID,
p.sName,
p.tID,
p.idValue,
ROW_NUMBER() OVER (PARTITION BY p.fID, p.sName ORDER BY p.tID) AS ID
FROM @t AS t
UNPIVOT (
idValue
FOR tID IN (t.[tID1], t.[tID2], t.[tID3], t.[tID4], t.[tID5], t.[tID6], t.[tID7])
) AS p
) AS e
GROUP BY e.fID,
e.sName
ORDER BY e.fID,
e.sName[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 02:47:51

As a sidenote, your desired output do not match your sample data.

E 12°55'05.25"
N 56°04'39.16"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-04-09 : 13:56:15

Thanks peso!!
Will the query work in 2000.Since i may have to use the same query for sqlserver2000.
Can we rewrite the query which should be compatible for 2000 and 2005 versions....

Thanks for your help in advance !!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 14:42:49

No, it will not work for SQL Server 2000.
Do you think it is funny to have us work for nothing?
If you want a solution for SQL Server 2000, please post in a SQL Server 2000 forum.

Use jhocutt's suggestion.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources