Pivot query

Question

Pivot query

FANINDRA BHORTAKKE

SSC Journeyman

Points: 88
More actions
March 2, 2010 at 8:59 am

#96897

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

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.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

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:.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

vax750(Hyung-Joo Kim) Valued Member Points: 72 More actions · Answer 4

-- 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 )

..