March 2, 2010 at 8:59 am
Hi All,
I have two tables say Product and ProductDetails as follows:
Product
------------
ID Name
-----------
1 Mango
2 Orange
-----------
ProductDetails
ID Type Code
---------------
1 1 abcd
1 2 efgh
1 3 sdkl
2 1 mkcl
--------------------
I want to design query so that it gives me result:
---------------------------------------
ID Name Code1 Code2 Code3..........
---------------------------------------
1 Mango abcd efgh sdkl
2 Orange mkcl NULL NULL
---------------------------------
Thanks in Advance,
Fanindra
March 3, 2010 at 9:23 am
Check the article in my signature about crosstabs/pivots. It explains how to make a crosstab query which will work in 2000.
Also, you might want to take a peek at the post on how to provide sample data for future inquiries.
March 3, 2010 at 9:29 am
Garadin (3/3/2010)
Check the article in my signature about crosstabs/pivots. It explains how to make a crosstab query which will work in 2000.Also, you might want to take a peek at the post on how to provide sample data for future inquiries.
I have to agree with Seth on both counts.
Seth, Haven't seen you around in a while, hope things have been okay.
March 3, 2010 at 9:37 am
Nice to see you too Lynn.
Things have been fine, I've just been really busy. Too busy working to post during the day, too busy playing CoD4 to post at home :hehe:.
March 17, 2010 at 8:01 am
-- dynamic sql
/*
select *
into #Product
from (
select 1 ID, 'Mango' Name union all
select 2 ID, 'Orange' Name union all
select 3 ID, 'Apple' Name ) r
select *
into #ProductDetails
from (
select 1 ID, 1 Type ,'abcd' Code union all
select 1 ID, 2 Type ,'efgh' Code union all
select 1 ID, 3 Type ,'sdkl' Code union all
select 2 ID, 1 Type ,'mkcl' Code union all
select 3 ID, 50 Type ,'fgfdd' Code
) r
*/
declare @sql varchar(2000)
set @sql = 'select a.id , b.Name '
select @sql = @sql + ', max( case when a.type = ' + convert(varchar, (number + 1 ) ) + ' then a.code else '''' end) [code' + + convert(varchar, (number + 1 ) ) + ']'
from master.dbo.spt_values where type = 'P'
and number < ( select MAX(Type) from #ProductDetails )
select @sql = @sql + ' from #ProductDetails a , #Product b where a.id = b.id group by a.id , b.Name'
exec(@sql )
..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply