June 24, 2009 at 2:33 pm
EDIT: Please scroll down to Post #742587; I've rephrased the scenario to remove Ambiguity.
Hi ,
Hoping someone can help with this.
I need the result of a Join that would typically be returned under multiple rows to be returned under a multiple fixedcolumns.
Here is some simplified test data:
CREATE TABLE #tempClient (
CID int Primary Key,
Name varchar(50)
)
INSERT INTO #tempClient
SELECT 1, 'Bob'
UNION ALL SELECT 2, 'Roger'
UNION ALL SELECT 3, 'John'
CREATE TABLE #tempBank(
BID int,
CID int,
AccountNumber varchar(20),
BranchName varchar(20),
BranchCode varchar(5),
AccountHolder varchar (30)
)
INSERT INTO #tempBank
SELECT 1, 100, '1000001', 'BinaryBranch', '10-10', 'Mr Bob'
UNION ALL SELECT 200, 1, '2000', 'HeadBranch', '20-00', 'Mr Bob'
UNION ALL SELECT 1,3, '2000310', 'SinkingBranch', '01-01', 'Mr John'
DROP TABLE #tempClient
DROP TABLE #tempBank
I need the result to look like this
First Row:
Client: Bob
Bank1: 100
Bank1BranchName: BinaryBranch
Bank1AccountNumber: 1000001
Bank2: 200
Bank2BranchName: HeadBranch
Bank2AccountNumber: 2000
Second Row:
Client: John
Bank1: 100
Bank1BranchName: BinaryBranch
Bank1AccountNumber: 1000001
Bank2: n/a
Bank2BranchName: n/a
Bank2AccountNumber: na
All help is appreciated!
June 24, 2009 at 6:14 pm
Thank you for the code - it makes helping out so much easier.
The #tempClient table looks pretty straight-forward. That's not the case with the #tempBank table. Normally, I would say that the CID column is the link between the tables. However, for CID 1, the record is not the first bank in your desired output.
Based on the sample data, how do you determine that BID 1 is the first bank for CID 1?
Trying to help.....
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 25, 2009 at 12:29 am
Hi Wayne ... sorry about that i switched my keys :crazy:
Below is the more accurate sample.
In the included SELECT you will notice there are two rows for Bob. I need it to be displayed as a single row for Bob but with both bank's details included.
In my real world scenario it may happen that there are more than 2 Banks linked to the client but I only need two.
Thanks for taking the time to help.
CREATE TABLE #tempClient (
CID int Primary Key,
Name varchar(50)
)
INSERT INTO #tempClient
SELECT 1, 'Bob'
UNION ALL SELECT 2, 'Roger'
UNION ALL SELECT 3, 'John'
CREATE TABLE #tempBank(
BID int,
CID int,
AccountNumber varchar(20),
BranchName varchar(20),
BranchCode varchar(5),
AccountHolder varchar (30)
)
INSERT INTO #tempBank
SELECT 1, 1, '1000001', 'BinaryBranch', '10-10', 'Mr Bob'
UNION ALL SELECT 2, 1, '2000', 'HeadBranch', '20-00', 'Mr Bob'
UNION ALL SELECT 1,3, '2000310', 'SinkingBranch', '01-01', 'Mr John'
SELECT *
FROM #tempClient as c
LEFT JOIN
#tempBank as b
ONc.CID = b.CID
DROP TABLE #tempClient
DROP TABLE #tempBank
June 25, 2009 at 1:11 am
Grinja (6/25/2009)
In my real world scenario it may happen that there are more than 2 Banks linked to the client but I only need two.
Hi,
This may be similar requirement,
Please ref this link: http://www.sqlservercentral.com/Forums/Topic740705-338-1.aspx#bm741350
And if you have 3 rows means, how you sort to get the 2 rows (ie by top 2 or by max,min or what way)
ARUN SAS
June 25, 2009 at 1:15 am
This ought to get you on the road...
select Client = c.Name,
Bank1 = max(case when b.bid = 1 then BID else NULL end),
B1Name = max(case when b.bid = 1 then BranchName else 'N/A' end),
B1Acct = max(case when b.bid = 1 then AccountNumber else 'N/A' end),
Bank2 = max(case when b.bid = 2 then BID else NULL end),
B2Name = max(case when b.bid = 2 then BranchName else 'N/A' end),
B2Acct = max(case when b.bid = 2 then AccountNumber else 'N/A' end)
from #tempClient c
INNER JOIN #tempBank b ON c.cid = b.cid
group by c.Name
edit: put code in code block
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 25, 2009 at 8:02 am
Thanks very much for taking time to answer. Im still not able to accomplish what I intended (but then again I'm not the brightest crayon in the box 😛 )
Your suggestions did bring an idea that I would like to attemp:
Is it possible to Count the rows grouped by CLID and add a column for that count?
For example in a my original SELECT (using two columns for brevity) the result would include a column with rows per CLID:
Name........BranchName..........BankGroupCount
----------------------------------------------
Bob.........BinaryBranch..................1
Bob.........HeadBranch...................2
Roger.........null............................1
John........SinkingBranch................1
If for example Bob had more than two banks then the BankGroupCount would incrementally increase per row. The Count must reset back to 1 with each client.
Thanks again.
June 25, 2009 at 2:15 pm
Grinja (6/25/2009)
Thanks very much for taking time to answer. Im still not able to accomplish what I intended
The code I posted produces the results that you requested. What did you forget to tell us? How is it not doing what you intended?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 26, 2009 at 1:14 am
Hi Wayne,
I have overly simplified my sample; in my real world sample BID is GUID (with multiple rows) and I will not be able to use it as you have in your code.
Thanks again for your help.
I will relook at my problem and repost when I can better describe this case.
EDIT: Removed Request to close thread
June 26, 2009 at 6:11 am
I've spent a bit of time to rework my sample code and redefine my problem (@ARUN SAS - thanks for that link)
Ok here goes.
My Problem:
From the sample data below I need to get each banks' details grouped by CLID on a single row.
Expected Result:
The result should look similar to the result from the SELECT query (in the sample data below) except for the following:
1. I need to provide for upto 5 banks. @arun-2 SAS- You mentioned using TOP ... I wasn't able to succesfully use it.
2. If only a single banks details exist then all other columns to read 'n/a'.
Thanks!
Sample table data and Code:
CREATE TABLE #tempBank
(
BankrowID int PRIMARY KEY,
CLID int ,
Branch varchar(20),
AccountNo varchar(20)
)
INSERT INTO #tempBank
SELECT 1, 1, 'SunnyVale', '0123'
UNION ALL
SELECT 2, 1, 'Hollywood', '102345'
UNION ALL
SELECT 3, 1, 'HaleView', '123456'
UNION ALL
SELECT 4, 1, 'DaisyLane', '551110'
UNION ALL
SELECT 5, 1, 'TigerWood', '221123'
UNION ALL
SELECT 6, 2, 'HazyHills', '12234'
--What I have so far:
SELECT
#tempBank.CLID,
MIN(Branch) AS Branch1,
MIN(AccountNo) AS Account1,
MAX(Branch) AS Branch2,
MAX(AccountNo) AS Account2
-- AS Branch3
-- AS Account3
-- AS Branch4
-- AS Account4
-- AS Branch5
-- AS Account5
FROM #tempBank
GROUP BY CLID
DROP TABLE #tempBank
June 26, 2009 at 10:42 pm
Grinja (6/26/2009)
Expected Result:The result should look similar to the result from the SELECT query (in the sample data below) except for the following:
1. I need to provide for upto 5 banks. @arun-2 SAS- You mentioned using TOP ... I wasn't able to succesfully use it.
2. If only a single banks details exist then all other columns to read 'n/a'.
Hi,
Try this
CREATE TABLE #tempBank
(
BankrowID int PRIMARY KEY,
CLID int ,
Branch varchar(20),
AccountNo varchar(20)
)
INSERT INTO #tempBank
SELECT 1, 1, 'SunnyVale', '0123'
UNION ALL
SELECT 2, 1, 'Hollywood', '102345'
UNION ALL
SELECT 3, 1, 'HaleView', '123456'
UNION ALL
SELECT 4, 1, 'DaisyLane', '551110'
UNION ALL
SELECT 5, 1, 'TigerWood', '221123'
UNION ALL
SELECT 6, 2, 'HazyHills', '12234'
UNION ALL
SELECT 7, 2, 'HazyHill', '122344'
UNION ALL
SELECT 8, 2, 'HazyHil', '1223444'
UNION ALL
SELECT 9, 3, 'HazyHi', '12234444'
UNION ALL
SELECT 10, 3, 'HazyH', '122344444'
Doing the step 01
select 0 SLNO,* into #temp from #tempBank
Doing the step 02
declare @slno int,@CLID int
set @slno = ''
set @CLID = ''
update #temp
set @slno = SLNO = (case when (SLNO @slno)and (@CLID CLID) then 0 else @slno end)+1
,@CLID = CLID
select Clid,max(Branch1)Branch1,max(Account1)Account1,
max(Branch2)Branch2,max(Account2)Account2,
max(Branch3)Branch3,max(Account3)Account3,
max(Branch4)Branch4,max(Account4)Account4,
max(Branch5)Branch5,max(Account5)Account5
from (
select clid,
(case when Slno = 1 then Branch else '.N/A' end)AS Branch1,
(case when Slno = 1 then AccountNo else '-' end)AS Account1,
(case when Slno = 2 then Branch else '.N/A' end)AS Branch2,
(case when Slno = 2 then AccountNo else '-' end)AS Account2,
(case when Slno = 3 then Branch else '.N/A' end)AS Branch3,
(case when Slno = 3 then AccountNo else '-' end)AS Account3,
(case when Slno = 4 then Branch else '.N/A' end)AS Branch4,
(case when Slno = 4 then AccountNo else '-' end)AS Account4,
(case when Slno = 5 then Branch else '.N/A' end)AS Branch5,
(case when Slno = 5 then AccountNo else '-' end)AS Account5
from #temp
) AS X
group by Clid
THE '.N/A','-' is must in the Max for the group class instead of the 'N/A'
ARUN SAS
June 29, 2009 at 3:53 am
Thanks ARUN, this worked for my scenario.
Thanks everyone for taking time to help me out.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply