Pivot query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Pivot query

Pivot query

Rate Topic

Display Mode

Topic Options

Author

Message

FANINDRA BHORTAKKE

Posted Tuesday, March 02, 2010 8:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 29, 2010 8:06 PM
Points: 4, Visits: 24

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

Post #875240

Garadin

Posted Wednesday, March 03, 2010 9:23 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

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

Post #876095

Lynn Pettis

Posted Wednesday, March 03, 2010 9:29 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 21,602, Visits: 27,425

Garadin (3/3/2010)

I have to agree with Seth on both counts.

Seth, Haven't seen you around in a while, hope things have been okay.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #876100

Garadin

Posted Wednesday, March 03, 2010 9:37 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

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

Post #876107

vax750(Hyung-Joo Kim)

Posted Wednesday, March 17, 2010 8:01 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 28, 2013 5:41 AM
Points: 12, Visits: 251

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

..

Post #884675

« Prev Topic | Next Topic »

Permissions