Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot query Expand / Collapse
Author
Message
Posted Tuesday, March 2, 2010 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 3, 2010 9:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #876095
Posted Wednesday, March 3, 2010 9:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
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.



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
Posted Wednesday, March 3, 2010 9:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
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 .



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

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #876107
Posted Wednesday, March 17, 2010 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse