SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert columns into one row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sara07
Starting Member

4 Posts

Posted - 07/30/2008 :  18:04:30  Show Profile  Reply with Quote
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 Date

Antoian Family 5 93923 Edward Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Janet Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Kyle Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Meredith Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Timothy Antoian 2008-05-12 00:00:00.000
Bilgin Family 4 276480 Cem Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Murat Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Onay Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Tuana Bilgin 2008-04-26 00:00:00.000

Select 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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 07/31/2008 :  04:32:32  Show Profile  Reply with Quote
Hi sara

You 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2008 :  05:40:08  Show Profile  Reply with Quote
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 Date

Antoian Family 5 93923 Edward Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Janet Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Kyle Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Meredith Antoian 2008-05-12 00:00:00.000
Antoian Family 5 93923 Timothy Antoian 2008-05-12 00:00:00.000
Bilgin Family 4 276480 Cem Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Murat Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Onay Bilgin 2008-04-26 00:00:00.000
Bilgin Family 4 276480 Tuana Bilgin 2008-04-26 00:00:00.000

Select 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 methods

http://www.projectdmx.com/tsql/rowconcatenate.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000