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.
| Author |
Topic |
|
sara07
Starting Member
4 Posts |
 Posted - 2008-07-30 : 18:04:30
|
| Hello, I have this query and this is printing data like this:However I would like it to give same salutation but I want All of the first names and last names to be in one row. The column names can be first_name, second_name, third_name, fourth_name, fifth_name.. depending on how many counts there are.. Is this possible?The reason is because I am doing mail merge from excel. Otherwise it is printing Antoin Family 5 times. I would like to see all of the names in one row so I can print on one page. Please help!Salutation_Line Count acct# FirstName LastName Exp DateAntoian Family 5 93923 Edward Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Janet Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Kyle Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Meredith Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Timothy Antoian 2008-05-12 00:00:00.000Bilgin Family 4 276480 Cem Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Murat Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Onay Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Tuana Bilgin 2008-04-26 00:00:00.000Select distinct C.Last_Name + ' ' + 'Family' as Salutation_Line, T.Account_Number_Count, C.Account_Number, C.First_Name, C.Last_Name, C.MPC_Member_Expiration into #Tmp_Extra_Members_Family from Dmm_Customer_Cube C inner join #Tmp_Account_Number T on T.Account_Number = C.Account_Number inner join #Tmp_Extra_Members E on T.Account_Number = E.Account_Number where T.Account_Number_Count >= 3 --and C.Primary_Household_Flag = 'Y' --and C.Account_Number = 84182 and C.MPC_Member_Expiration between '3/1/2008' and '5/30/2008' |
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-31 : 04:32:32
|
Hi saraYou can do this in SQL but it is a pain. The reason it is a pain is because it violates some basic relational tenets; SQL also does not handle this sort of thing well. Excel, however, loves pivoting things here, there and everywhere. You would be much better advised to carry on as you are and then pivot the data in Excel. Use hammers to bang in nails and screwdrivers to turn screws  |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-31 : 05:40:08
|
quote:
Originally posted by sara07
Hello, I have this query and this is printing data like this:However I would like it to give same salutation but I want All of the first names and last names to be in one row. The column names can be first_name, second_name, third_name, fourth_name, fifth_name.. depending on how many counts there are.. Is this possible?The reason is because I am doing mail merge from excel. Otherwise it is printing Antoin Family 5 times. I would like to see all of the names in one row so I can print on one page. Please help!Salutation_Line Count acct# FirstName LastName Exp DateAntoian Family 5 93923 Edward Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Janet Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Kyle Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Meredith Antoian 2008-05-12 00:00:00.000Antoian Family 5 93923 Timothy Antoian 2008-05-12 00:00:00.000Bilgin Family 4 276480 Cem Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Murat Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Onay Bilgin 2008-04-26 00:00:00.000Bilgin Family 4 276480 Tuana Bilgin 2008-04-26 00:00:00.000Select distinct C.Last_Name + ' ' + 'Family' as Salutation_Line, T.Account_Number_Count, C.Account_Number, C.First_Name, C.Last_Name, C.MPC_Member_Expiration into #Tmp_Extra_Members_Family from Dmm_Customer_Cube C inner join #Tmp_Account_Number T on T.Account_Number = C.Account_Number inner join #Tmp_Extra_Members E on T.Account_Number = E.Account_Number where T.Account_Number_Count >= 3 --and C.Primary_Household_Flag = 'Y' --and C.Account_Number = 84182 and C.MPC_Member_Expiration between '3/1/2008' and '5/30/2008'
see this for some of the methodshttp://www.projectdmx.com/tsql/rowconcatenate.aspx |
|
|
|
|
|
|
|
|
Insert columns into one row