sql query help sqlserver 2000 - 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 2000 Forums

SQL Server Development (2000)

sql query help sqlserver 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-08-13 : 15:48:11

[code]

I need the a query to get the desired output.

DECLARE @MT TABLE (
TID bigint,
CFLG char(1),
TRSN char(2),
TROTH varchar(20))

INSERT @MT

SELECT 127,'C','01','' UNION ALL
SELECT 127,'C','04','' UNION ALL
SELECT 127,'F','01','' UNION ALL
SELECT 127,'F','04',''

Output:

TID CFLG C01 C04 CTROTH FCFLG F01 F04 FTROTH
--- --- --- --- ---- ----- --- ---- -----
127 C 01 04 null F 01 04 null

[/code]

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 16:39:53

if this is all you need then by hook n crook we can do this

but if you have more then hmmm it will be something else
THIS IS NOT RELIABLE if you have more data

DECLARE @MT TABLE (
TID bigint,
FLG char(1),
TRSN char(2),
TROTH varchar(20))

INSERT @MT

SELECT 127,'C','01','' UNION ALL
SELECT 127,'C','04','' UNION ALL
SELECT 127,'F','01','' UNION ALL
SELECT 127,'F','04',''

select distinct TID, CFLG, C01, C04, CTROTH, FCFLG, F01,F04, FTROTH from
(select CFLG = case when FLG = 'C' then FLG END from @MT) as A, @MT,
(select FCFLG = case when FLG = 'F' then FLG END from @MT) as B,
(select C01 = case when FLG = 'C' and TRSN = '01' then TRSN END from @MT) as C,
(select C04 = case when FLG = 'C' and TRSN = '04' then TRSN END from @MT) as D,
(select F01 = case when FLG = 'F' and TRSN = '01' then TRSN END from @MT) as E,
(select F04 = case when FLG = 'F' and TRSN = '04' then TRSN END from @MT) as F,
(select CTroth = case when FLG ='C' then 'NULL' end from @MT) as G,
(select FTroth = case when FLG ='C' then 'NULL' end from @MT) as H

where TID is not null and
CFLG is not null and
FCFLG is not null and
C01 is not null and
C04 is not null and
F01 is not null and
F04 is not null and
CTROTH is not null and
FTROTH is not null

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources