Many Rows into 1 - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Many Rows into 1

Author

Topic

ttruheyo
Starting Member

9 Posts

Posted - 2011-10-20 : 09:55:55

Here is my dilemma that I am hoping that I can get some assistance with. First let me say this,
I did not create this database it was created by a third party and I now have to create reports from it.
If I run the following:

SELECT AC.Account_Name, ACC.AccountContact_ContactType, CT.Contact_Name, CT.Contact_Title 
FROM Accounts AC INNER JOIN 
AccountContacts ACC ON AC.Account_ID = ACC.AccountContact_Account_ID INNER JOIN 
Contacts CT ON ACC.AccountContact_Contact_ID = CT.Contact_ID 
ORDER BY CL.Client_Name

My Results are:

Client_Name AccountContact_ContactType Contact_Name Contact_Title
Client1 Type1 NameA Name_Title1
Client1 Type2 NameB Name_Title2
Client1 Type3 NameC Name_Title3
Client2 Type2 NameA Name_Title2
Client3 Type3 NameA Name_Title3

Where what I need is:

Account_Name AccountContact_ContactType1 Contact_Name1 Contact_Title1 AccountContact_ContactType2 Contact_Name2 Contact_Title2 Group3, Group4...
Account1 Type1 NameA Name_Title1 Type2 NameB Name_Title2 .......

I have tried different Pivot options with no luck and different groupings. I mus be doing something wrong.

Ideas?

NotTheDBA - But playing one.
Tom <ttruheyo@Gmail.com>

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-20 : 19:21:53

I can help but only if there is a max number of contacts per account.
Pete

Likes to run, hates the runs!

ttruheyo
Starting Member

9 Posts

Posted - 2011-10-20 : 20:32:56

Max Number of Contacts per account would be 4

NotTheDBA - But playing one.
Tom <ttruheyo@Gmail.com>

ttruheyo
Starting Member

9 Posts

Posted - 2011-10-21 : 11:38:44

Is there no one that can help?

NotTheDBA - But playing one.
Tom <ttruheyo@Gmail.com>

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-10-21 : 11:40:50

Pivot is the answer. Can you show us what you have tried?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-21 : 13:46:26

As promised, old school but it should work for you.

SELECT AC.Account_Name, ACC.AccountContact_ContactType, CT.Contact_Name, CT.Contact_Title, 0 as pos
into #tmptbl
FROM Accounts AC INNER JOIN
AccountContacts ACC ON AC.Account_ID = ACC.AccountContact_Account_ID INNER JOIN
Contacts CT ON ACC.AccountContact_Contact_ID = CT.Contact_ID
ORDER BY AC.Account_Name

/* to simulate data for my test
--drop table #tmptbl
create table #tmptbl (Account_Name char(30),AccountContact_ContactType char(30), Contact_Name char(30), Contact_Title char(30),pos int)
insert into #tmptbl values ('Client1', 'Type1', 'NameA', 'Name_Title1',0)
insert into #tmptbl values ('Client1', 'Type2', 'NameB', 'Name_Title2',0)
insert into #tmptbl values ('Client1', 'Type3', 'NameC', 'Name_Title3',0)
insert into #tmptbl values ('Client2', 'Type2', 'NameA', 'Name_Title2',0)
insert into #tmptbl values ('Client3', 'Type3', 'NameA', 'Name_Title3',0)
*/

declare @AccountName char(30)
declare @pos int
set @pos = 0
set @accountname = ' '

update #tmptbl
set @pos = pos = case when @accountName = Account_Name then @pos + 1 else 1 end,
@accountname = Account_Name

select account_name,
max(case when pos = 1 then AccountContact_ContactType else '' end) as AccountContact_ContactType1,
max(case when pos = 1 then Contact_Name else '' end) as Contact_Name1,
max(case when pos = 1 then Contact_Title else '' end) as Contact_Title1,

max(case when pos = 2 then AccountContact_ContactType else '' end) as AccountContact_ContactType2,
max(case when pos = 2 then Contact_Name else '' end) as Contact_Name2,
max(case when pos = 2 then Contact_Title else '' end) as Contact_Title2,

max(case when pos = 3 then AccountContact_ContactType else '' end) as AccountContact_ContactType3,
max(case when pos = 3 then Contact_Name else '' end) as Contact_Name3,
max(case when pos = 3 then Contact_Title else '' end) as Contact_Title3,

max(case when pos = 4 then AccountContact_ContactType else '' end) as AccountContact_ContactType4,
max(case when pos = 4 then Contact_Name else '' end) as Contact_Name4,
max(case when pos = 4 then Contact_Title else '' end) as Contact_Title4
from #tmptbl
group by account_name

Likes to run, hates the runs!

ttruheyo
Starting Member

9 Posts

Posted - 2011-10-21 : 14:05:45

OK I seem to have gotten the pivot table to work. However, I am not 100 % sure how to use the data that I have created.

SELECT *
  FROM (
    SELECT
      Contact_name,
      (CASE  
       WHEN Contact_Type = 'Executive' THEN 1
        WHEN Contact_Type =   'Billing' THEN 2
        WHEN Contact_Type =   'Governance' THEN 3
        WHEN Contact_Type =  'Primary Onsite' THEN  4
        WHEN Contact_Type =   'Primary IT' THEN 5
        WHEN Contact_Type =   'Secondary IT'  THEN 6
        WHEN Contact_Type =   'Technical' THEN 7
        WHEN Contact_Type =  'Onsite Contact' THEN 8
        WHEN Contact_Type =   'Other'  THEN 9
       ELSE NULL END) as [ContactType],
      Client_Name
    FROM CI1
  ) TableDate
  PIVOT (
    Count(Client_Name)
    FOR [ContactType] IN (
      [1],[2],[3],[4],
      [5],[6],[7],,
      [9]
    )
  ) PivotTable

I Get:
Contact_name 1 2 3 4 5 6 7 8 9
NAME1 3 0 0 0 0 0 0 0 0
NAME2 0 0 0 0 1 0 0 0 0
NAME3 0 0 0 0 0 0 0 1 0
NAME4 2 1 0 0 0 0 0 0 0
NAME5 0 1 0 0 0 0 0 0 0
NAME6 0 0 0 1 0 0 0 0 0
NAME7 1 1 0 0 0 0 0 0 0
NAME8 0 0 0 0 31 0 0 0 0

If I run this:
[CODE]
SELECT *
FROM (
SELECT
Contact_name,
(CASE
WHEN Contact_Type = 'Executive' THEN 1
WHEN Contact_Type = 'Billing' THEN 2
WHEN Contact_Type = 'Governance' THEN 3
WHEN Contact_Type = 'Primary Onsite' THEN 4
WHEN Contact_Type = 'Primary IT' THEN 5
WHEN Contact_Type = 'Secondary IT' THEN 6
WHEN Contact_Type = 'Technical' THEN 7
WHEN Contact_Type = 'Onsite Contact' THEN 8
WHEN Contact_Type = 'Other' THEN 9
ELSE NULL END) as [ContactType],
Client_Name, Client_ID
FROM CI1
) TableDate
PIVOT (
Count(Client_ID)
FOR [ContactType] IN (
[1],[2],[3],[4],
[5],[6],[7],(8),
[9]
)
) PivotTable[/CODE]
I Get this:

Contact_name Client_Name 1 2 3 4 5 6 7 8 9

NAME1

Here is the Database diagram if that helps anyone

NotTheDBA - But playing one.
Tom <ttruheyo@Gmail.com>

ttruheyo
Starting Member

9 Posts

Posted - 2011-10-21 : 17:03:15

pduffin
YOU ARE THE BEST... THANK YOU SO MUCH I Will keep this in my lock box for future use.

NotTheDBA - But playing one.
Tom <ttruheyo@Gmail.com>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources