PIVOT table needed? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

PIVOT table needed?

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-07 : 12:21:38

Greetings SQL friends!

I have the following data :

betting_opportunity----------result_number----------result_id
559632-----------------------4----------------------2763415
559632-----------------------0----------------------2763416
456617-----------------------1----------------------2763423
456617-----------------------3----------------------2763424

what I want to do is display the data like this :

betting_opportunity----------final_result
559632-----------------------(4-0)
456617-----------------------(1-3)

I tried some ideas with case statements but failed to find a solution. Your help would be appreciated!

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-07 : 12:38:57

Can there be more than 2 results for a betting_opportunity?

Why do we return (in your sample results) 4-0 instead of 0-4 ? Does it make a difference which is returned? What is the logic?

- Jeff
http://weblogs.sqlteam.com/JeffS

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-07 : 12:43:24

quote:
Originally posted by jsmith8858

Can there be more than 2 results for a betting_opportunity?

Why do we return (in your sample results) 4-0 instead of 0-4 ? Does it make a difference which is returned? What is the logic?

- Jeff
http://weblogs.sqlteam.com/JeffS

Hi Jeff,

There can only be two results for a betting opportunity as this is the results for a football (soccer for you Yanks

) betting opportunity.
As you can see from the result_id, the result_number which has the lower resul_id is the number of goals scored by the home side.

So for 559632 the final score is 4-0 as result_number 4 has a lower result_id than result_number 0

Hope this makes sense.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 13:16:58

[code]-- prepare sample data
declare @data table (betting_opportunity int, result_number int, result_id int)

insert @data
select 559632, 4, 2763415 union all
select 559632, 0, 2763416 union all
select 456617, 1, 2763423 union all
select 456617, 3, 2763424

-- Show the data
SELECT d.Betting_Opportunity,
'(' + CAST(r1.Result_Number AS VARCHAR) + '-' + CAST(r2.Result_Number AS VARCHAR) + ')' AS Final_Result
FROM (
SELECT Betting_Opportunity,
MIN(Result_ID) AS minresid,
MAX(Result_ID) AS maxresid
FROM @Data
GROUP BY Betting_Opportunity
) AS d
INNER JOIN @Data AS r1 ON r1.Result_ID = d.minresid
INNER JOIN @Data AS r2 ON r2.Result_ID = d.maxresid
ORDER BY d.Betting_Opportunity DESC[/code]

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-08 : 04:55:54

quote:

Originally posted by Peso

-- prepare sample data
declare @data table (betting_opportunity int, result_number int, result_id int)

insert	@data
select	559632, 4, 2763415 union all
select	559632, 0, 2763416 union all
select	456617, 1, 2763423 union all
select	456617, 3, 2763424

-- Show the data
SELECT		d.Betting_Opportunity,
		'(' + CAST(r1.Result_Number AS VARCHAR) + '-' +  CAST(r2.Result_Number AS VARCHAR) + ')' AS Final_Result
FROM		(
			SELECT		Betting_Opportunity,
					MIN(Result_ID) AS minresid,
					MAX(Result_ID) AS maxresid
			FROM		@Data
			GROUP BY	Betting_Opportunity
		) AS d
INNER JOIN	@Data AS r1 ON r1.Result_ID = d.minresid
INNER JOIN	@Data AS r2 ON r2.Result_ID = d.maxresid
ORDER BY	d.Betting_Opportunity DESC

Peter Larsson
Helsingborg, Sweden

Thanks peso. I now understand the logic of your post. I have managed to use your logic to solve another problem to sort out the lotto draw numbers.

Welldone.

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:01:04

Does the Lotto numbers have anything to do with this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75750

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-08 : 05:06:07

quote:
Originally posted by Peso

Does the Lotto numbers have anything to do with this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75750

Peter Larsson
Helsingborg, Sweden

Hi Peso,

I don't think so, as you can see from user name I am a different member and I didn't make any contribution to that post.

My situation as as follows :

Each lotto bet is a unique betting opportunity but for some reason in the source data each number ddrawn (total of 7) a new line ccreated with a new result_id. As you can see, each draw will have 7 lines with 7 unique result_id. What I wanted to achieve is one line with a string representing the drawn numbers. Something like :

Betting_opportunity------------Draw
5421433------------------------25, 6, 1, 33, 41, 44, 3

etc...

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:15:36

There are many concatenation functions here at SQLTeam. They are found with a simple search.

The query you have to make later is something like

SELECT DISTINCT Betting_Opportunity, dbo.fnConcat(Betting_Opportunity)
FROM YourTable

With this kind of function, you can even have the numbers sorted ascending, and not rely to result_id

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:22:04

[code]CREATE TABLE MyTable (Betting_Opportunity INT, Result_ID INT, Number TINYINT)

INSERT MyTable
SELECT 5421433, 234, 25 union all
SELECT 5421433, 534545, 6 union all
SELECT 5421433, 2342, 1 union all
SELECT 5421433, -234, 33 union all
SELECT 5421433, 3423, 41 union all
SELECT 5421433, 9767, 44 union all
SELECT 5421433, 1, 3 union all
SELECT 541433, 2334, 1 union all
SELECT 541433, 5, 35 union all
SELECT 541433, 234235, 16 union all
SELECT 541433, -23435, 24 union all
SELECT 541433, 342335, 11 union all
SELECT 541433, 976735, 7 union all
SELECT 541433, 135, 28
GO
CREATE FUNCTION dbo.fnMyFunction
(
@Betting_Opportunity INT
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @s VARCHAR(20)

SELECT @s = ISNULL(@s + ',', '') + CAST(Number AS VARCHAR)
FROM (
SELECT DISTINCT TOP 100 PERCENT Number
FROM MyTable
WHERE Betting_Opportunity = @Betting_Opportunity
ORDER BY Number
) AS d

RETURN @s
END
GO

SELECT DISTINCT Betting_Opportunity,
dbo.fnMyFunction(Betting_Opportunity)
FROM MyTable
GO

DROP TABLE MyTable
DROP FUNCTION fnMyFunction[/code]

Peter Larsson
Helsingborg, Sweden

Antonio
Posting Yak Master

168 Posts

Posted - 2007-02-08 : 05:39:11

quote:

Originally posted by Peso

CREATE TABLE MyTable (Betting_Opportunity INT, Result_ID INT, Number TINYINT)

INSERT	MyTable
SELECT	5421433, 234, 25 union all
SELECT	5421433, 534545, 6 union all
SELECT	5421433, 2342, 1 union all
SELECT	5421433, -234, 33 union all
SELECT	5421433, 3423, 41 union all
SELECT	5421433, 9767, 44 union all
SELECT	5421433, 1, 3 union all
SELECT	541433, 2334, 1 union all
SELECT	541433, 5, 35 union all
SELECT	541433, 234235, 16 union all
SELECT	541433, -23435, 24 union all
SELECT	541433, 342335, 11 union all
SELECT	541433, 976735, 7 union all
SELECT	541433, 135, 28
GO
CREATE FUNCTION dbo.fnMyFunction
(
	@Betting_Opportunity INT
)
RETURNS VARCHAR(20)
AS
BEGIN
	DECLARE	@s VARCHAR(20)

	SELECT	@s = ISNULL(@s + ',', '') + CAST(Number AS VARCHAR)
	FROM	(
			SELECT DISTINCT	TOP 100 PERCENT Number
			FROM		MyTable
			WHERE		Betting_Opportunity = @Betting_Opportunity
			ORDER BY	Number
		) AS d

	RETURN	@s
END
GO

SELECT		DISTINCT Betting_Opportunity,
		dbo.fnMyFunction(Betting_Opportunity)
FROM		MyTable
GO

DROP TABLE MyTable
DROP FUNCTION fnMyFunction

Peter Larsson
Helsingborg, Sweden

Thanks for all the help Pesso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources