January 12, 2006 at 10:30 am
Hi
I have a table which has differnt persons name and credit card deatils.A person can have more than one credit card. The table is like thi
Name CreditCardNo ExpDt
Arun 1234567 10/08
Arun 3456787 11/09
Amit 5634566 12/09
Amit 2345565 12/12
Now I want the results like this
Name CreditCard1 CreditCard2
Arun 1234567 3456787
Amit 5634566 2345565
January 12, 2006 at 10:41 am
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=249767
Check this one and look at David's post (the last one). Depending on your data you may have to modify this to fit your needs but it should point you in the right direction.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 14, 2006 at 2:27 am
On the first table write a trigger. When ever a new record is added the trigger will look for exisiting name and add the new crdit card number in the second table. You can take out the report whenevner wanted from the second table
V.kadal Amutham
January 16, 2006 at 5:34 am
Hi
But I have to get all the data from one table only.Now the scenario is like I have one name_id field and on this there can be 10 cards of a same guy and I
want all the card deatils as columns.The sample data is like this
CREDIT_CARD_ID,NAME_ID,CREDIT_CARD_TYPE,CREDIT_CARD_NUMBER,CREDIT_CARD_NAME,CREDIT_CARD_EXPIRATION_DATE,CREDIT_LIMIT
123456 427617 AX 20490615 UNKNOWN 31/10/2004 NULL
134567 427617 AX 23061005 UNKNOWN 31/10/2004 NULL
987654 427617 AX 49061005 UNKNOWN 31/10/2004 NULL
976543 427617 AX 29311004 UNKNOWN 31/12/1999 NULL
786543 427617 AX 29312002 UNKNOWN 31/12/2001 NULL
234534 427617 AX 67310042 UNKNOWN 31/12/2004 NULL
998877 427617 AX 49062003 UNKNOWN 31/10/2004 NULL
675645 427617 AX 84371009 UNKNOWN 31/10/2006 NULL
457663 427617 AX 49063001 UNKNOWN 31/10/2006 NULL
876543 427617 AX 84371009 UNKNOWN 31/10/2006 NULL
876543 623120 AX 563801005 Abcdxer 31/07/2004 NULL
765432 623120 AX 17812010 Abcdxer 30/06/2004 NULL
987654 623120 AX 67813026 Abcdxer 31/07/2006 NULL
987654 623120 AX 33801005 Abcdxer 31/07/2004 NULL
765434 623120 AX 23812002 Abcdxer 31/07/2006 NULL
505144 623120 AX 17813026 Abcdxer 31/07/2003 NULL
583037 623120 AX 56381013 Abcdxer 30/04/2007 NULL
587558 623120 VA 53642022 Abcdxer 31/07/2006 NULL
Now I want the result like this
NameID CREDIT_CARD_ID1 Credit_card_number1 CRedit_card_id2 Credit_card_number2 and so on.
January 16, 2006 at 11:28 am
did you just post real client data credit cards on a forum? i hope the data you pasted is fake developer data.
edit the post and remove the data right away.
Lowell
January 16, 2006 at 12:01 pm
Hi
That was fake data but still I have removed it.
January 18, 2006 at 7:55 am
Any help on this..I'm stuck.
January 18, 2006 at 8:16 am
Will this solve your problem?
declare @table table(Name varchar(10), CreditCardNo int)
insert @table select 'Arun', 1234567
insert @table select 'Arun', 3456787
insert @table select 'Amit', 5634566
insert @table select 'Amit', 2345565
select Name, min(CreditCardNo), max(CreditCardNo) from @table group by Name
January 18, 2006 at 8:27 am
Oh, I realise that you want up to 10 credit card numbers on one line. Here is a solution with 3 numbers:
declare @table table(Name varchar(10), CreditCardNo int)
insert @table select 'Arun', 1234567
insert @table select 'Arun', 3456787
insert @table select 'Arun', 34567872
insert @table select 'Amit', 5634566
insert @table select 'Amit', 2345565
insert @table select 'Amit', 23455653
declare @table2 table(Id int identity(1,1), Name varchar(10), CreditCardNo int)
insert @table2 (Name, CreditCardNo) select Name, CreditCardNo from @table
order by Name, CreditCardNo -- or something else instead of CreditCardNo, depending on how data should be sorted
select dt.Name, t1.CreditCardNo, t2.CreditCardNo, t3.CreditCardNo
from
(select Name, min(Id) as minid from @table2 group by Name)
dt
left join @table2 t1
on t1.Name = dt.Name and t1.Id = dt.minid
left join @table2 t2
on t2.Name = dt.Name and t2.Id = dt.minid + 1
left join @table2 t3
on t3.Name = dt.Name and t3.Id = dt.minid + 2
January 18, 2006 at 9:22 am
i think the only way to do this is to use a function, and have the function return ONE column with all the values in a comma delimited format:
CREATE FUNCTION dbo.udf_ReturnAllCreditCards (@NAME_ID int)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @RetStr varchar(4000)
SELECT
@RetStr =
ISNULL(
@RetStr
+ ', '
+ isnull(CONVERT(varchar(20),CREDIT_CARD_NUMBER),''),'')
FROM CARD_DETAILS
WHERE (NAME_ID = @NAME_ID)
RETURN @RetStr END
SELECT *,dbo.udf_ReturnAllCreditCards (NAME_ID) as AllCreditCards
FROM customers
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply