Query help - Pivot maybe?

Question

Post reply

Query help - Pivot maybe?

qldrobbo

Newbie

Points: 3
More actions
December 9, 2009 at 10:29 am

#214658

Hi,
Say I have two tables
Trades with column TradeId
MapTraders with columns TradeId, TraderId and Type
In the Trades table say I have 1 row with TradeId 1
In the MapTraders table I have 2 rows
TradeId 1, TraderId 1, Type 'Trader'
TradeId 1, TraderId 2, Type 'ExecutionTrader'
Now I want a query that returns the results from Trades table such as
TradeId, Trader, ExecutionTrader
1 1 2
If I do an inner join, it is of course going to produce
TradeId, TraderId, Type
1 1 'Trader'
1 2 'ExecutionTrader'
Now I know I can get the results how I want them using sub queries in the select clause, but I was wondering if there is a better way, maybe using pivot tables? I had a look but they seem to only work with aggregating numbers, not simply making rows into columns.
Any help appreciated.
Rob

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

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

qldrobbo Newbie Points: 3 More actions · Answer 1

Is using Row_Number a good way to do this?

Such as

WITH TradersTable AS (

SELECT ROW_NUMBER() OVER (PARTITION BY TradeId ORDER BY Type) AS RowNum,

T.Id,

TraderId,

Type

FROM Trades T

LEFT JOIN MapTradesTraders MTT ON T.Id = MTT.TradeId)

SELECT T1.Id,

T1.TraderId AS ExecutionTrader,

T2.TraderId AS Trader

FROM TradersTable T1 ON T.Id = T1.Id

LEFT JOIN TradersTable T2 ON T1.RowNum + 1 = T2.RowNum

AND T1.Id = T2.Id

WHERE T1.Type = 'Execution Trader'

ORDER BY Id

Is this a good way to do what I want? Better or worse than subqueries in the select clause?

MaxBrackett Grasshopper Points: 16 More actions · Answer 2

If you use SQL Report Server and the matrix format, it'll do it all for you.

Or you could use this SQL Pivot

http://msdn.microsoft.com/en-us/library/ms177410.aspx

qldrobbo Newbie Points: 3 More actions · Answer 3

As stated in my original post, I don't know if I can use the pivot because it only seems to work by aggregating, eg using count or max etc, I don't want to do that, I want all entries to be displayed

LutzM SSC Guru Points: 107049 More actions · Answer 4

The aggregation used within the PIVOT clause is either to eliminate NULL values (e.g. MAX clause) or to SUM or COUNT the actaul number of rows.

You'll still get all your entries as long as you define the columns that would identify one row additionally to your PIVOT columns.

To stay with the example as per BOL (2K5, I'm not sure if still identical in 2K8):

Data are pivoted per EmployeeID [164], [198], [223], [231], [233], having VendorID as a row identifier. If you'd add let's say column ShipMethodID to the inner and outer select clause the result will be different. Just give it a try.

It's confusing, but it works... 😉

If you're unsure whether PIVOT will give you the expected result you might want to have a look into CrossTab (please see the link in my signature for further details).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 5

qldrobbo (12/9/2009)
Hi,
Say I have two tables
Trades with column TradeId
MapTraders with columns TradeId, TraderId and Type
In the Trades table say I have 1 row with TradeId 1
In the MapTraders table I have 2 rows
TradeId 1, TraderId 1, Type 'Trader'
TradeId 1, TraderId 2, Type 'ExecutionTrader'
Now I want a query that returns the results from Trades table such as
TradeId, Trader, ExecutionTrader
1 1 2
If I do an inner join, it is of course going to produce
TradeId, TraderId, Type
1 1 'Trader'
1 2 'ExecutionTrader'
Now I know I can get the results how I want them using sub queries in the select clause, but I was wondering if there is a better way, maybe using pivot tables? I had a look but they seem to only work with aggregating numbers, not simply making rows into columns.
Any help appreciated.
Rob

Your new, Rob. Welcome aboard.

As you can see, even with the relatively simple problem that you've posted, there is some bit of confusion as to what you want. There's also not a whole lot of working code being offered. People are just pointing you to articles so you can help yourself. Wanna know why?

Take a gander at the article at the first link in my signature below. Don't be put off by the title. Seriously... read and practice the article for future questions and people will trip over themselves trying to help you better and quicker.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)