Single result set - 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)

Single result set

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-08-28 : 14:48:06

I dont know if what I want to do is even possible. I have something like the following fields:

FirstName
LastName
MemberNumber
AccountDesc
AccountBalance

Each record could have many accounts. The data is easy enough to retrieve like this:

John Smith 1000 Acct2 25.25
John Smith 1000 Acct3 36.98
John Smith 1000 Acct4 84.27

But of course, I dont want it that way. :) I want it this way:
John Smith 1000 Acct2 25.25 Acct3 36.98 Acc4 84.27

Is this possible?

Thanks,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:59:38

[code]-- Prepare sample data
DECLARE @Sample TABLE (FirstName SYSNAME, LastName SYSNAME, MemberNumber INT, AccountDesc SYSNAME, AccountBalance MONEY)

INSERT @Sample
SELECT 'John', 'Smith', 1000, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct4', 84.27

-- Show the expected output
SELECT DISTINCT s1.FirstName,
s1.LastName,
s1.MemberNumber,
STUFF((SELECT TOP 100 PERCENT ';' + s2.AccountDesc + ' ' + CONVERT(VARCHAR, s2.AccountBalance, 0) FROM @Sample AS s2 WHERE s2.MemberNumber = s1.MemberNumber ORDER BY s2.AccountDesc FOR XML PATH('')), 1, 1, '') AS Accounts
FROM @Sample AS s1
ORDER BY s1.MemberNumber[/code]

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

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-08-28 : 21:50:48

I posted this too quickly and did not read the forum descriptions enough. I have been looking for this all over my help and books, and then realized that I posted in teh SQL2005 forum. I dont want to re-post, but I will if it would be easier. I am using SQL 2000. I dont see this "FOR XML PATH" and I get syntax error currently so I am assuming it is a function in 2005 that I currently do not have. Is there a solution that is available in 2000?
Thanks,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 01:46:43

Yes, there are many concatenation functions available here at SQLTeam.
They provide the same functionality as above.

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

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-29 : 06:37:27

Hi,
DECLARE @Sample TABLE (FirstName SYSNAME, LastName SYSNAME, MemberNumber INT, AccountDesc Varchar(100), AccountBalance varchar(1000))

INSERT @Sample
SELECT 'John', 'Smith', 1000, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct4', 84.27

Select * From @sample

Declare @Acct Varchar(1000), @number int

Declare @Temp Table (FirstName varchar(100),LastName varchar(100),MemberNumber int, AccBal varchar(1000))

Insert into @Temp
Select distinct p.FirstName, p.LastName, p.MemberNumber, P.AccountDesc + ' '+ p.AccountBalance as 'AccBal' from @sample P
Inner join @sample S on P.MemberNumber = S.MemberNumber

Update @Temp
Set @Acct = AccBal = Case when @number = MemberNumber Then @acct + ' '+AccBal Else AccBal End, @number = MemberNumber
Select FirstName, LastName, MemberNumber, Max(AccBal) From @Temp Group by FirstName, LastName, MemberNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 06:43:50

Ranganath, do that with this sample data.

INSERT @Sample
SELECT 'John', 'Smith', 1000, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct4', 84.27 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct4', 84.27

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

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-29 : 06:55:55

Hi,

DECLARE @Sample TABLE (FirstName SYSNAME, LastName SYSNAME, MemberNumber INT, AccountDesc Varchar(100), AccountBalance varchar(1000))

INSERT @Sample
SELECT 'John', 'Smith', 1000, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Smith', 1000, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct4', 84.27 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct2', 25.25 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct3', 36.98 UNION ALL
SELECT 'John', 'Doe', 1001, 'Acct4', 84.27

Select * From @sample

Declare @Acct Varchar(1000), @number int

Declare @Temp Table (FirstName varchar(100),LastName varchar(100),MemberNumber int, AccBal varchar(1000))

Insert into @Temp
Select distinct p.FirstName, p.LastName, p.MemberNumber, P.AccountDesc + ' '+ p.AccountBalance as 'AccBal' from @sample P
Inner join @sample S on P.MemberNumber = S.MemberNumber

Update @Temp
Set @Acct = AccBal = Case when @number = MemberNumber Then @acct + ' '+AccBal Else AccBal End, @number = MemberNumber
Select FirstName, LastName, MemberNumber, Max(AccBal) From @Temp Group by FirstName, LastName, MemberNumber

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 07:17:45

Nice!
When I add a clustered identity in the @temp table, the code works.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources