help with query (making 2 rows 1 row)

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 2005 Forums

Transact-SQL (2005)

help with query (making 2 rows 1 row)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 00:02:38

Hi,

I have the following SPROC which returns the correct data, but I would like to change the format in which they are returned. Currently its going to bring back either 0,1,or 2 rows.

Any help is much appreciated!!

thanks once again :)
mike123

Sample Data currently brought back:

voteForID / voterID / date /points

410 1371 2007-08-17 00:35:00 100
1371 410 2007-08-30 20:40:00 95

I would like to bring it back as 1 column as seen below here

voteForID_1 /voterID_1 / date_1 / points_1 / voteForID_2 / voterID_2 /date_2 / points_2

410 / 1371 / 2007-08-17 00:35:00 / 100 / 1371 / 410 / 2007-08-30 20:40:00 / 95

SPROC:

CREATE PROCEDURE [dbo].[select_userVotes_Exchanged]
(
@userID int,
@voteForID int
)

AS SET NOCOUNT ON

SELECT voteForID,voterID,date,points from tbluservote where (voteForID = @userID and voterID = @voteForID) or (voterID = @userID and voteForID = @voteForID)

GO

Table Structure:

CREATE TABLE [dbo].[tblUserVote](
[voteID] [int] IDENTITY(1,1) NOT NULL,
[voteForID] [int] NOT NULL,
[voterID] [int] NOT NULL,
[date] [smalldatetime] NOT NULL,
[points] [tinyint] NOT NULL)

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 01:49:19

--sample sp returns two rows
Create procedure #test1
as
Select --voteForID / voterID / date /points

410 as voteForID, 1371 as voterID, '2007-08-17 00:35:00' as [date], 100 as points union all
select 1371, 410, '2007-08-30 20:40:00', 95

--table to sttore the sp value
Create table #t (rownum int identity, voteForID int, voterID int, [date] datetime,points int)
insert into #t
exec #test1

--req result
Select
Max(Case when rownum = 1 then voteForID End) as voteForID_1,
Max(Case when rownum = 1 then voterID End) as voterID_1,
Max(Case when rownum = 1 then [date] End) as date_1,
Max(Case when rownum = 1 then points End) as points_1,
Max(Case when rownum = 2 then voteForID End) as voteForID_2,
Max(Case when rownum = 2 then voterID End) as voterID_2,
Max(Case when rownum = 2 then [date] End) as date_2,
Max(Case when rownum = 2 then points End) as points_2
from #t

--------------------------------------------------
S.Ahamed

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 02:07:35

hi PbGuy,

Sorry I'm not sure I understand this? I'm not sure its what I am looking for. I can't seem to execute it properly or get the results I am looking for. Please advise

thanks,
mike123

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:13:13

Execute the procedure first then the create table script and then the query then u will get some idea.

1. The create procedure is for my reference(u told ur SP will return 2 rows as an example for me) and it returns two rows,
2. I am inserting the SP values to a temporary table with identity
3. Using identity, i am displaying the result in one row.

--------------------------------------------------
S.Ahamed

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 02:21:56

Hi PbGuy,

Ok I get what you are doing now, wierd I had errors when trying before.

How can I integrate this into my SPROC ? Do I have to use a temp table? This is what I have so far, but I get errors on each "rownum"

Server: Msg 207, Level 16, State 1, Procedure select_userVotes_Exchanged, Line 14
Invalid column name 'rownum'.

Thanks very much for any help!
mike123

CREATE PROCEDURE [dbo].[select_userVotes_Exchanged]
(
@userID int,
@voteForID int
)

AS SET NOCOUNT ON

--req result
Select
Max(Case when rownum = 1 then voteForID End) as voteForID_1,
Max(Case when rownum = 1 then voterID End) as voterID_1,
Max(Case when rownum = 1 then [date] End) as date_1,
Max(Case when rownum = 1 then points End) as points_1,
Max(Case when rownum = 2 then voteForID End) as voteForID_2,
Max(Case when rownum = 2 then voterID End) as voterID_2,
Max(Case when rownum = 2 then [date] End) as date_2,
Max(Case when rownum = 2 then points End) as points_2

FROM tbluservote WHERE (voteForID = @userID and voterID = @voteForID) or (voterID = @userID and voteForID = @voteForID)

GO

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:24:18

ohh...do you want the SP to return the result as one row right???

--------------------------------------------------
S.Ahamed

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:32:27

CREATE PROCEDURE [dbo].[select_userVotes_Exchanged]
(
@userID int,
@voteForID int
)
AS
declare @test table(rownum int identity, voteForID int, voterID int, [date] datetime,points int)

insert into @test
SELECT voteForID,voterID,date,points from tbluservote
where (voteForID = @userID and voterID = @voteForID) or (voterID = @userID and voteForID = @voteForID)

Select
Max(Case when rownum = 1 then voteForID End) as voteForID_1,
Max(Case when rownum = 1 then voterID End) as voterID_1,
Max(Case when rownum = 1 then [date] End) as date_1,
Max(Case when rownum = 1 then points End) as points_1,
Max(Case when rownum = 2 then voteForID End) as voteForID_2,
Max(Case when rownum = 2 then voterID End) as voterID_2,
Max(Case when rownum = 2 then [date] End) as date_2,
Max(Case when rownum = 2 then points End) as points_2
from @test

GO

--------------------------------------------------
S.Ahamed

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 02:47:46

ok great I think this will work, for performance reasons, is there a better way to do it than with a temp table ?

thanks again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 02:57:50

Something like this, perhaps?

CREATE PROCEDURE dbo.uspSelect_UserVotes_Exchanged
( 
	@UserID INT,
	@VoteForID INT
)
AS

SET NOCOUNT ON 

SELECT		v1.VoteForID AS VoteForID_1,
		v1.VoterID AS VoterID_1,
		v1.Date AS Date_1,
		v1.Points AS Points_1,
		v2.VoteForID AS VoteForID_2,
		v2.VoterID AS VoterID_2,
		v2.Date AS Date_2,
		v2.Points AS Points_2
FROM		dbo.tblUserVote	AS v1
INNER JOIN	dbo.tblUserVote	AS v2 ON v2.VoterID = v1.VoteForID
			AND v2.VoteForID = v1.VoterID
WHERE		v2.VoterID > v1.VoterID
ORDER BY	v1.VoterID

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 02:57:51

Hey, pbguy ..

actually I am looking at this query again, it might not be returning exactly what I want. Reason being is this part

"
SELECT voteForID,voterID,date,points from tbluservote
where (voteForID = @userID and voterID = @voteForID) or (voterID = @userID and voteForID = @voteForID)
"

This doesnt specify an ORDER BY.

I need this row (voteForID = @userID and voterID = @voteForID) to be voteForID_1,voterID_1,date_1,points_1

and (voterID = @userID and voteForID = @voteForID) to be the second.
voteForID_2,voterID_2,date_2,points_2

I want to name the columns according the the ORDER BY , not according to which order they are brought back.

Thanks for your continued assistance :)

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-31 : 03:07:34

Hey Peso,

Just a second behind ya :) This is brilliant, and exactly what I am looking for .

Thanks so much!!!!!!! Have a great day

Cheers,
Mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources