A strange T-SQL challenge

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » A strange T-SQL challenge

27 posts, Page 1 of 3

1 2 3 »»»

A strange T-SQL challenge

Rate Topic

Display Mode

Topic Options

Author

Message

meichner

Posted Wednesday, November 05, 2008 1:37 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 12:43 PM
Points: 215, Visits: 640

I have been asked to do something that I have never done in T-SQL before. In order to describe what I need done I am going to provide a fictious example. I have a table called Quantities. This table has three fields:

ID int IDENTITY
QuoteNumber int NOT NULL,
Quantity int NOT NULL

I need to create a select statement that will return all of the Quantities in one row for a specific Quote Number. In otherwords I need the output of the select statement to look like:

QuoteNumber, Qty1, Qty2, Qty3, Qty4

For the sake of this example I know that there will never be more then 4 items in the table for a specific quote number.

Any help creating this select statement (or getting me started) will be much appreciated.

Thanks

Post #597668

Garadin

Posted Wednesday, November 05, 2008 1:40 PM

SSCommitted

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

Take a look at the PIVOT command.

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

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 #597672

meichner

Posted Wednesday, November 05, 2008 3:15 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 12:43 PM
Points: 215, Visits: 640

Unfortunately my boss rejected the idea of using a pivot table (I can't really explain why). Is there another way to do this?

Thanks. I really appreciate the help.

Post #597728

noeld

Posted Wednesday, November 05, 2008 3:42 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

meichner (11/5/2008)

Unfortunately my boss rejected the idea of using a pivot table (I can't really explain why). Is there another way to do this?

Thanks. I really appreciate the help.

The suggestion was to use The PIVOT SQL SERVER Relational Operator; not to be confused with "PIVOT Tables".

* Noel

Post #597744

Matt Miller (#4)

Posted Wednesday, November 05, 2008 3:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 PM
Points: 6,998, Visits: 13,949

Actually, before you even tackle either option, you have the slightly smaller challenge of assigning which value within a quote gets to be qt1 vs qt2 vs qt3 vs qt4. That's a running total, which you will have to implement first.

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Just so you have something to pivot on......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #597749

meichner

Posted Wednesday, November 05, 2008 5:30 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 12:43 PM
Points: 215, Visits: 640

noeld (11/5/2008)

meichner (11/5/2008)

Unfortunately my boss rejected the idea of using a pivot table (I can't really explain why). Is there another way to do this?

Thanks. I really appreciate the help.

The suggestion was to use The PIVOT SQL SERVER Relational Operator; not to be confused with "PIVOT Tables".

Sorry, I understood the suggestion. I just mis worded my post.

Post #597778

Matt Miller (#4)

Posted Wednesday, November 05, 2008 6:38 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 PM
Points: 6,998, Visits: 13,949

Then I would say - you have me at a loss. What you're requesting (meaning the output) IS a pivot operation, but your manager doesn't want a pivot, so - I'm not sure what can be done. What IS the objection to Pivot?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #597800

Jeff Moden

Posted Wednesday, November 05, 2008 7:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

Probably because it's relatively slower than a Cross-Tab and not backwards compatible to SQL Server 2000 and may not be migratable to other RDBMS's.

So... without any real data to test on nor anyway to discern the difference between QTY1, QTY2, QTY3, or QTY4 (please identify if some order is important and what that order would be based on), the only thing I can do is point you to an article about Cross-Tabs...

http://www.sqlservercentral.com/articles/T-SQL/63681/

... and an article on how to post data to get better, tested answers more quickly...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #597822

RBarryYoung

Posted Wednesday, November 05, 2008 9:25 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

What is the maximum number of Quantity's per QuoteNumber?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #597861

meichner

Posted Thursday, November 06, 2008 5:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 12:43 PM
Points: 215, Visits: 640

rbarryyoung (11/5/2008)

What is the maximum number of Quantity's per QuoteNumber?

The Maxiumum number is 4.

Any help would be appreciated.

Thanks

Post #598027

« Prev Topic | Next Topic »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions