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

Transact-SQL (2005)

Grouping rows

Author

Topic

san3297
Starting Member

7 Posts

Posted - 2010-07-12 : 16:32:01

I have plain sql query as shown below

SELECT acct,premise,reid,read where reid=@reid and acct=@acct
when i pass reid=1 and acct=170529 i get rows as

acct premise reid read
175029 1750 1 264
175029 1750 1 88
175029 1750 1 20

when i pass reid=2 and acct=170529 i get rows as
acct premise reid read
175029 1750 2 43
175029 1750 2 90
175029 1750 2 96

Now i want to modify the query as such when i pass reid=-1 and acct=170529 i should get the result set as

acct premise reid1 reid2 read1 read2
175029 1750 1 2 264 43
175029 1750 1 2 88 90
175029 1750 1 2 20 96

Note that there are only 2 reids in table 1 and 2. I want combine information of reid1 and reid2 for a account in one single row. Any suggestions on how to achieve this.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 17:01:12

i would take a look at madhivanan's dynamic pivot

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:03:01

I would 1st create a new procedure like this (which assumes that there is always a reid = 1


Create Proc aNewProc
	@acct int
AS
SET NOCOUNT ON

SELECT	t1.acct, t1.premise, reid1, reid2, read1, read2
FROM (
	SELECT	acct, premise, reid as reid1, [read] as read1
	FROM	yourTable
	where	reid = 1
	and	acct - @acct
) t1
LEFT JOIN (
	SELECT	acct, premise, reid as reid2, [read] as read2
	FROM	yourTable
	where	reid = 2
	and	acct - @acct
) t2
On	t1.acct = t2.acct
And	t1.premise = t2.premise;
GO

Then I would modify the existing proc to call it when reid = -1 like this

ALTER Proc yourOldProc
	@reid int,
	@acct int
AS
SET NOCOUNT ON

If @reid = -1
Begin
	Exec aNewProc @acct
	Return
End
... rest of your code

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 17:10:26

russell's is nice! why don't you pass in NULL instead and use COAELSCE or ISNULL which will make it one sproc with no IF


create table pleasesupplydata(acct int, premise int, reid int, [read] int)

INSERT INTO pleasesupplydata
values(175029, 1750, 1, 264 )

INSERT INTO pleasesupplydata
values(175029, 1750, 1, 88 )
INSERT INTO pleasesupplydata
values(175029, 1750, 1, 20 )
INSERT INTO pleasesupplydata
values(175029, 1750, 2, 43 )
INSERT INTO pleasesupplydata
values(175029, 1750, 2, 90 )
INSERT INTO pleasesupplydata
values(175029, 1750, 2, 96)

declare @acct int = null

SELECT	t1.acct, t1.premise, reid1, reid2, read1, read2
FROM (
	SELECT	acct, premise, reid as reid1, [read] as read1
	FROM	pleasesupplydata
	where	reid = 1
	and	acct = COALESCE( @acct, acct)
) t1
LEFT JOIN (
	SELECT	acct, premise, reid as reid2, [read] as read2
	FROM	pleasesupplydata
	where	reid = 2
	and	acct = COALESCE( @acct, acct)
) t2
On	t1.acct = t2.acct
And	t1.premise = t2.premise;

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 17:18:07

seems like there is one more field missing that 'links' rows

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

san3297
Starting Member

7 Posts

Posted - 2010-07-13 : 09:55:25

acct premise reid1 reid2 read1 read2
175029 1750 1 2 264 43
175029 1750 1 2 264 90
175029 1750 1 2 264 96
175029 1750 1 2 88 43
175029 1750 1 2 88 90
175029 1750 1 2 88 96
175029 1750 1 2 20 43
175029 1750 1 2 20 90
175029 1750 1 2 20 96

The above query is returning me 9 rows instead of 3. Any suggestion on what iam doing wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources