July 18, 2012 at 7:02 am
Hi Friends,
Table1
RPTTypeCode Firstname LastName BarrowerID LoanTransID
.................................................................................
B1 Anu k 1 1
C1 Swetha SS 2 1
Table 2
LoanAppID LoanTransID LoanNumber
-----------------------------------------------------
1423 1 ABC12345
I need below fields in single row for a LoanransID
LoanNumber,B1.FirstName,B1.LastName,C1.FirstName,C2.LastName
Any body can help me to write sql query?
Thanks
Swetha
July 18, 2012 at 7:08 am
Sample data in consumable format
DECLARE @Table1 TABLE (RPTTypeCode CHAR(2), Firstname VARCHAR(50), LastName VARCHAR(50), BarrowerID INT, LoanTransID INT)
INSERT INTO @Table1 VALUES ('B1','Anu','k',1,1),('C1','Swetha','SS',2,1)
DECLARE @Table2 TABLE (LoanAppID INT, LoanTransID INT, LoanNumber VARCHAR(10))
INSERT INTO @Table2 VALUES (1423,1,'ABC12345')
July 18, 2012 at 7:16 am
Please help me??
July 18, 2012 at 7:19 am
If you could read the second link in my signature on posting code it will help us out in future as we will have readily consumable data to which anyone can pick up and start testing without having to determin what youe schema and data look like.
But a dirty solution based on your sample data provided will do the trick.
SELECT
T2.LoanNumber,
B1.FirstName,
B1.Lastname,
C1.FirstName,
C1.LastName
FROM
@Table2 T2
INNER JOIN
(
SELECT
LoanTransID,
FirstName,
LastName
FROM
@Table1
WHERE
RPTTypeCode = 'B1'
) B1
ON
T2.LoanTransID = B1.LoanTransID
INNER JOIN
(
SELECT
LoanTransID,
FirstName,
LastName
FROM
@Table1
WHERE
RPTTypeCode = 'C1'
) C1
ON
T2.LoanTransID = C1.LoanTransID
July 18, 2012 at 7:29 am
Thank you so much!!!!!
July 18, 2012 at 11:12 pm
I need a query without hardcoding RPTTypeCode column fields like B1,C1..etc.
Any one can help??
July 18, 2012 at 11:44 pm
From another thread:
Swetha527 (7/18/2012)
CREATE TABLE [dbo].[Trans1]([RPTTypeCode] [char](2) NULL,
[Firstname] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[BarrowerID] [int] NULL,
[LoanTransID] [int] NULL
) ON [PRIMARY]
insert into [Trans1] values('B1','Swathi','B',10,1),('C1','Anu','CH',11,1),('E1','Chintu','KK',13,1)
CREATE TABLE [dbo].[Trans2](
[LoanAppID] [int] NULL,
[LoanTransID] [int] NULL,
[LoanNumber] [varchar](10) NULL
) ON [PRIMARY]
insert into [Trans2] values(1,1,'ABC123')
I want query output as below in sinlge row.
LoanNumber, B1.FirstName, B1.LastName,C1.FirstName,C1.LastName,E1.FirstName,E2.LastName
I do not want to hardcode "RPTTypeCode" column values because sometimes i get more values in the RPTTypeCode column of Trans1 table.
Anybody can help me please???
July 19, 2012 at 12:00 am
Is this what you were looking for?
select
t2.[LoanNumber],
stuff((select ', ' + t1.RPTTypeCode + '.' + t1.FirstName + ',' + t1.RPTTypeCode + '.' + t1.LastName
from dbo.Trans1 t1
where t1.LoanTransID = t2.LoanTransID
for xml path(''),type).value('.','varchar(max)'),1,2,'') CombinedData
from
dbo.Trans2 t2;
July 19, 2012 at 12:08 am
No.
I need columns and data like
[LoanNumber] B1.FirstName B1.LastName C1.FirstName C1.LastName E1.FirstName E1.LastName
-----------------------------------------------------------------------------------------
ABC12345 Swathi B Anu Ch Chintu kk
July 19, 2012 at 12:17 am
Swetha527 (7/19/2012)
No.I need columns and data like
[LoanNumber] B1.FirstName B1.LastName C1.FirstName C1.LastName E1.FirstName E1.LastName
-----------------------------------------------------------------------------------------
ABC12345 Swathi B Anu Ch Chintu kk
Then I suggest you start reading the articles I have referenced below in my signature block regarding Cross Tabs and Pivots. There are two articles, and you should read both of them in depth.
They should help you figure out how to answer your question. If you still have questions after reading the articles, please be sure to ask here.
July 19, 2012 at 12:23 am
I did read the same. But i am unable to write dynamic script same. Please help me.
Thanks
Swetha
July 19, 2012 at 12:25 am
I did read it. But i am unable to write dynamic script for the same. Please help me.
July 19, 2012 at 12:36 am
Swetha527 (7/19/2012)
I did read it. But i am unable to write dynamic script for the same. Please help me.
If you can't write dynamic sql, I'm not going to write it for you. You need to be able to understand and support the code. This is only going to happen if you try to write it and ask for specific help along the way.
We are volunteers, and we are here to help, not do your work for you.
July 19, 2012 at 1:03 am
I think you are unable to provide the dynamic script for the scenario i have given.
Thank you.
July 19, 2012 at 7:48 am
Swetha527 (7/19/2012)
I think you are unable to provide the dynamic script for the scenario i have given.Thank you.
You can think what you like, but I'm not going to give you something that you obviously won't understand and will be unable to support if it needs changing or breaks in the future.
If you want help, you have to start by showing some initiative and provide us with DDL, sample data, expected results, and what code you have written in an effort to solve your problem.
We will help you and guide you, but we aren't here to do your work for you. Especially if you aren't willing to demonstrate some desire to help us solve your problem.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply