A strange T-SQL challenge

Question

Post reply

A strange T-SQL challenge

meichner

SSCertifiable

Points: 5563
More actions
November 5, 2008 at 1:37 pm

#200938

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

Viewing 15 posts - 1 through 15 (of 26 total)

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

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

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

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

meichner SSCertifiable Points: 5563 More actions · Answer 2

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.

noeld SSC Guru Points: 96590 More actions · Answer 3

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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/[/url]

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?

meichner SSCertifiable Points: 5563 More actions · Answer 5

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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.

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

meichner SSCertifiable Points: 5563 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

No problem... could you provide some test data in a readily consumable format like I asked in my previous post? See the link in my signature for how to easily do that. Thanks...

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

meichner SSCertifiable Points: 5563 More actions · Answer 11

Jeff Moden (11/6/2008)
No problem... could you provide some test data in a readily consumable format like I asked in my previous post? See the link in my signature for how to easily do that. Thanks...

Sorry, I took so long getting back to you. I was pulled onto something else.

The selection below yields the results below.

Select ID, QuoteNumber, Quantity

From Quantities

Where QuoteNumber = 225494

IDQuoteNumberQuantity

760225494 5000

761225494 7500

762225494 10000

763225494 6666

My boss wants to see the result as:

QuoteNumber Qty1 Qty2, Qty3 Qty4

225494 5000 7500 10000 6666

You can (hopefully) use the following to create the test data

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

ID int IDENTITY,

QuoteNumber int NOT NULL,

Quantity int NOT NULL,

)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 5000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 7500)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 10000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 6666)

My boss is against the pivot and crosstab methods because his background is as a flat file programmer and has difficulty understanding sql. If I can get this working, I think I can sell him on it.

Thanks for the help.

steveb. SSC-Forever Points: 46733 More actions · Answer 12

here is my attempt without using PIVOT, there may be a better way to do this but this seems to work. It will fail if there are more than 4 quotes though.

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

ID int IDENTITY,

QuoteNumber int NOT NULL,

Quantity int NOT NULL,

)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 5000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 7500)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 10000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 6666)

IF OBJECT_ID('TempDB..#tmptable','U') IS NOT NULL

DROP TABLE [#TmpTable]

CREATE TABLE #TmpTable

(TheOrder INT, Quotenumber INT, quantity INT, q1 INT,q2 INT, q3 INT, q4 INT)

INSERT INTO #tmptable ([Quotenumber],[quantity], [TheOrder])

( SELECT quotenumber, quantity,

ROW_NUMBER() OVER (PARTITION BY quotenumber ORDER BY id) AS TheOrder

FROM #mytable )

UPDATE #tmptable SET q1 = [quantity] WHERE [TheOrder] =1

UPDATE #tmptable SET q2 = [quantity] WHERE [TheOrder] =2

UPDATE #tmptable SET q3 = [quantity] WHERE [TheOrder] =3

UPDATE #tmptable SET q4 = [quantity] WHERE [TheOrder] =4

SELECT [Quotenumber], MAX(q1) AS Qty1, MAX(q2) AS Qty2,MAX(q3) AS Qty3,MAX(q4) AS Qty4 FROM #tmptable

GROUP BY [Quotenumber]

ORDER BY [Quotenumber]

noeld SSC Guru Points: 96590 More actions · Answer 13

meichner (11/6/2008)
Jeff Moden (11/6/2008)
No problem... could you provide some test data in a readily consumable format like I asked in my previous post? See the link in my signature for how to easily do that. Thanks...
Sorry, I took so long getting back to you. I was pulled onto something else.
The selection below yields the results below.
Select ID, QuoteNumber, Quantity
From Quantities
Where QuoteNumber = 225494
IDQuoteNumberQuantity
760225494 5000
761225494 7500
762225494 10000
763225494 6666
My boss wants to see the result as:
QuoteNumber Qty1 Qty2, Qty3 Qty4
225494 5000 7500 10000 6666
You can (hopefully) use the following to create the test data
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
ID int IDENTITY,
QuoteNumber int NOT NULL,
Quantity int NOT NULL,
)
Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 5000)
Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 7500)
Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 10000)
Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 6666)
My boss is against the pivot and crosstab methods because his background is as a flat file programmer and has difficulty understanding sql. If I can get this working, I think I can sell him on it.
Thanks for the help.

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

ID int IDENTITY,

QuoteNumber int NOT NULL,

Quantity int NOT NULL,

)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 5000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 7500)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 10000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 6666)

SELECT QuoteNumber,

[1], [2], [3], [4]

FROM

( SELECT QuoteNumber, Quantity,

ROW_NUMBER()OVER( PARTITION BY QuoteNumber ORDER BY ID) r

FROM #mytable

) AS Src

PIVOT

( MIN(Quantity)

FOR r IN ( [1], [2], [3], [4])

) AS PivotTable

* Noel

meichner SSCertifiable Points: 5563 More actions · Answer 14

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

CREATE TABLE #mytable

(

ID int IDENTITY,

QuoteNumber int NOT NULL,

Quantity int NOT NULL,

)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 5000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 7500)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 10000)

Insert Into #mytable(QuoteNumber, Quantity) Values(225494, 6666)

SELECT QuoteNumber,

[1], [2], [3], [4]

FROM

( SELECT QuoteNumber, Quantity,

ROW_NUMBER()OVER( PARTITION BY QuoteNumber ORDER BY ID) r

FROM #mytable

) AS Src

PIVOT

( MIN(Quantity)

FOR r IN ( [1], [2], [3], [4])

) AS PivotTable

This worked great. As I am new to pivots, I have a few questions.

1. Why does the pivot table need the aggregate function MIN in order to work?

2. Why did you need the column Row_number?

Thanks again.