SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sql query help sqlserver 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldba2k6
Posting Yak Master

176 Posts

Posted - 08/13/2007 :  15:48:11  Show Profile  Reply with Quote


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	


ashley.sql
Constraint Violating Yak Guru

USA
299 Posts

Posted - 08/13/2007 :  16:39:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000