Cross tab sql in sqlserver.

[kingstonr]

HI,
Can anybody help me to get cross tab query.
my table is like below.

id type remarks
4 FV TestFV
4 RC TEstRC
5 FV Test5FV.

I want the result as

id FVremarks RCremarks
4 TEstFV TestRC
5 Test5FV null.

pl some body help me in this query.

rgds
kingston

 

[AlexCuse]

If the only Remark types are FV and RC you coulsd try this, or expand upon as needed. The only problem is, if you are missing the field on the left side of the join (FVremarks in this case) you will not get any record for that ID. I can hardly say this is reliable code, but if you're in a pinch this could help you.

(select a.[ID], 
a.remarks as FVRemarks, 
b.remarks as RCRemarks
from (select [ID], Remarks from #TEST where Type = 'FV') a 
LEFT JOIN (select [ID], Remarks from #TEST where Type = 'RC') b 
on a.[ID] = b.[ID])

Your table is not really set up ideally for a cross tab, as you have to group by 1 value, yet get one or more corresponding values from the same column. You could possibly create a whole set of queries as a stored proc to get desired result?


Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[mrdenny]

I disagree, this table is setup great for a cross tab query.

For SQL 2000 you have to bang out the code manually.

select id,
   max(case when type = 'FV' then Remarks end) FVRemarks,
   max(case when type = 'RC' then Remarks end) RCRemarks
from table
group by id
ordery by id

For SQL 2005 you can use the pivot function.

select id, FVRemarks, RCRemarks
from table
PIVOT
(
MAX(Remarks)
FOR Type IN ('FV', 'RC')
) as pvt
ORDER BY id

The SQL 2000 will work fine, the SQL 2005 code may need tweaking. I don't have it totally memorized yet. You can read up more on the PIVOT command in BOL under PIVOT.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[AlexCuse]

Thanks for that Denny! I knew there had to be a better way

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com