?
Solved

SQL - Combine results into single line?

Posted on 2011-04-28
29
Medium Priority
?
983 Views
Last Modified: 2012-05-11
Here is the query I have so far:

select dbo.legal_entity.first_name, dbo.legal_entity_picklist.group_code, dbo.group_items.description
from dbo.legal_entity
INNER JOIN dbo.legal_entity_picklist
ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
INNER JOIN dbo.group_items
ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'

Here are the results:

first_name     group_code     description
Anthony        CONTCT           Last, First
Anthony        MAIL                 Holiday Party
Anthony       MAIL                  Newletter

All three of these values are for one person.  Is there any way I can combine this into one line in the results?  Maybe like this...

first_name       Contact           Holiday Party            Newletter
Anthony          Last, First        Y                              N

Y and N would depend on if the value exists in the results of the query... Hopefully someone gets the idea and can point me in the right direction.  I am not a SQL programmer.  Thank you.    
0
Comment
Question by:mattpayne59
  • 14
  • 12
  • 2
  • +1
29 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 35485973
I guess the number of descriptions vary, right? So I would suggest doing it dynamically. Check the query below:
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @cols1 varchar(max)




set @cols1 = stuff((select distinct  ', case when [' + dbo.group_items.description + '] is null then ''N'' else ''Y'' end as [' + dbo.group_items.description + ']' 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		order by 1 for xml path('')), 1, 2, '')


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		order by 1 for xml path('')), 1, 2, '') + ']'




set @strSQL = 'select first_name, group_code, ' + @cols1 + ' from (
			select 	dbo.legal_entity.first_name, 
				dbo.legal_entity_picklist.group_code, 
				dbo.group_items.description
			from dbo.legal_entity
			INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
			INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
			where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		) o
		pivot (count(event_no) for description in (' + @cols + ')) as p'

exec(@strSQL)

Open in new window

0
 
LVL 7

Expert Comment

by:JuanCarniglia
ID: 35486031
You need to use nested queries, for instance:

select dbo.legal_entity.first_name as first_name,
(select CASE dbo.group_items WHEN 'Holiday Party' THEN 'Y' ELSE 'N' END from dbo.group_items) as [Holiday Party],
(select CASE dbo.group_items WHEN 'Newletter' THEN 'Y' ELSE 'N' END from dbo.group_items) as [Newletter]
from dbo.legal_entity
where dbo.legal_entity.last_name = 'DePascale'
...

The problem here (and the associated performance cost) is that you have to join each sub-select statement in order to get the right data (not done in the above example).

Anyway, I hope you get the idea.
0
 
LVL 7

Expert Comment

by:JuanCarniglia
ID: 35486043
ralmada, your example is much better.

Bye
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:mattpayne59
ID: 35491594
I am getting this when I try to run it:

Invalid column name 'event_no'.

I see that in the pivot statement but I am not sure what that is supposed to do.
0
 

Author Comment

by:mattpayne59
ID: 35491964
Towards the end I changed event_no to description and it returned a result.  I now have:

first_name     group_code     Holiday Party     Newsletter     Last, First
Anthony        CONTCT          Y                        Y                    Y
Anthony        MAIL                Y                        Y                    Y

This is definitely closer.  But what I really want to see is:

first_name    Holiday Party     Newsletter     Contact Name
Anthony       Y                       Y                    Last, First

There can be multiple contact names listed so there would need to be some seperator there.  How can I merge this into one line?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35492384
Not sure what is the "Last, First" mean to you since it's in the description column. if you don't want the group_code there, then try
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @cols1 varchar(max)




set @cols1 = stuff((select distinct  ', case when [' + dbo.group_items.description + '] is null then ''N'' else ''Y'' end as [' + dbo.group_items.description + ']' 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		order by 1 for xml path('')), 1, 2, '')


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		order by 1 for xml path('')), 1, 2, '') + ']'




set @strSQL = 'select first_name, ' + @cols1 + ' from (
			select 	dbo.legal_entity.first_name, 
				dbo.legal_entity_picklist.group_code, 
				dbo.group_items.description
			from dbo.legal_entity
			INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
			INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
			where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		) o
		pivot (count(group_code) for description in (' + @cols + ')) as p'

exec(@strSQL)

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 35492522
ok, I think I got it,

try
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @cols1 varchar(max)




set @cols1 = stuff((select distinct  ', case when [' + dbo.group_items.description + '] is null then ''N'' else ''Y'' end as [' + dbo.group_items.description + ']' 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '')


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '') + ']'




set @strSQL = 'select first_name, ' + @cols1 + ', Contact_Name from (
			select 	dbo.legal_entity.first_name, 
				dbo.legal_entity_picklist.group_code, 
				case when dbo.legal_entity_picklist.group_code = ''CONTCT'' then description end as Contact_Name,
				dbo.group_items.description
			from dbo.legal_entity
			INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
			INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
			where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync'
		) o
		pivot (count(group_code) for description in (' + @cols + ')) as p
		where Contact_name is not null'
		
exec(@strSQL)

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35492687
This is still a bit off... I really appreciate your time helping me here.  

I changed the last name in the query to Peterson.  if I just run:

select * from dbo.legal_entity where last_name = 'Peterson'

I get 15 results.  So the query should have 15 results.  instead, it is only showing 2.  Here is what I get:

first_name   Email Alerts   Flooring     Contractor       Holiday Party      Newsletter     Contact_Name
Sam             Y                   Y                Y                     Y                        Y                     Last, First
Theodore     Y                   Y                Y                     Y                        Y                     Last, First

So it is not returning all of the values and then it seems to be putting a Y in even if should be a N.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35493123
>>I changed the last name in the query to Peterson.<<
There's three places you need to change that, have you done it in all of them?

Also, let's go one step at a time.
run this query and post the result here.


declare @strSQL varchar(max)
declare @cols varchar(max)


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'Peterson' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '') + ']'




set @strSQL = 'select first_name, ' + @cols + ', Contact_Name from (
			select 	dbo.legal_entity.first_name, 
				dbo.legal_entity_picklist.group_code, 
				case when dbo.legal_entity_picklist.group_code = ''CONTCT'' then description end as Contact_Name,
				dbo.group_items.description
			from dbo.legal_entity
			INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
			INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
			where dbo.legal_entity.last_name = ''Peterson'' and dbo.legal_entity.maint_user_id <> ''ExchSync''
		) o
		pivot (count(group_code) for description in (' + @cols + ')) as p'
		
exec(@strSQL)

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35493269
I am going to try and explain this and probably mess it up.  First of all, I forgot about the ExchSync part of the query... There should be 8 total rows, not 15.  But tere is still an issue.  When we do this join, it is only bringing in contacts that have values in both tables.  If there is no data at all in the other tables, I still need that value to show up but with 0s and NULLs across for the values.

Here is what I get from this:

first_name,Email Alerts,Flooring,Contactor,Holiday Party,Newsletter,Contact_Name
Devin,1,1,0,0,1,0,NULL
Michael,0,0,0,1,1,0,NULL
Sam,0,0,0,1,0,0,NULL
Theodore,0,01,1,1,1,NULL
Sam,0,0,0,0,0,0,Last,First
Theodire,0.0.0.0.0.0.Last,First

So we have duplicates when there should only be one and we are missing 4 people completely.
0
 

Author Comment

by:mattpayne59
ID: 35493299
I just changed it to a LEFT JOIN instead of INNER and that is better but still not 100% correct.  There are still duplicates for Sam and Theodore and another user who should have two only has 1.
0
 

Author Comment

by:mattpayne59
ID: 35493341
I also changed it to include legal_entity_sk in the select statements... so:



set @strSQL = 'select legal_entity_sk, first_name, ' + @cols + ', Contact_Name from (
      select dbo.legal_entity.legal_entity_sk, dbo.legal_entity.first_name,

is how it starts and that brought the second value in that was missing.  I now just have the duplicates... But I noticed that the duplicates are the ones that have Contact_Name filled in.  Is that the way it is supposed to work for the next part?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35493364
ok, so from what you're saying not everybody has a group code 'CONTCT', Devin for instance... correct?

also can you please clarify if the column legal_entity_sk is tied to the group_code one? I mean if legal_entity_sk = 1 for instance that means that we have group_code = 'CONTCT'?
 
0
 

Author Comment

by:mattpayne59
ID: 35493404
Correct.  Some dont have any of this "extra" stuff.

legal_entity_sk is a unique identifier used in the legal_entity table.  It joins the two tables together... It has nothing to do with values in group_code.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35493673
Would it be possible to give some sample data from the tables with expected result?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35495540
try this one now
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @cols1 varchar(max)


set @cols1 = stuff((select distinct  ', case when [' + dbo.group_items.description + '] = 0 then ''N'' else ''Y'' end as [' + dbo.group_items.description + ']' 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '')


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '') + ']'

set @strSQL = ';with CTE as (select 	dbo.legal_entity.first_name, 
										dbo.legal_entity_picklist.group_code, 
										dbo.group_items.description
							from dbo.legal_entity
							LEFT JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
							LEFT JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
							where dbo.legal_entity.last_name = ''DePascale'' and dbo.legal_entity.maint_user_id <> ''ExchSync''
				) 
				select first_name, ' + @cols1 + ', Contact_Name 
				from (
					select 	first_name, 
							group_code, 
							(select max(description) from CTE where first_name = t1.first_name and group_code = ''CONTCT'') as Contact_name,
							description
					from CTE t1
				) o
				pivot (count(group_code) for description in (' + @cols + ')) as p'
		
exec(@strSQL)

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35495639
I added legal_entity_sk to the select statements at the bottom.  One of the people should have been in there twice but I guess since all of the values were exactly the same, it cut one of them out. If i use legal_entity_sk which is unique, they both show.

Let me look at this a little over the weekend and run some more extensive tests.  I have to run this on a table with about 16,000 people in it and right now I am just using some very limited tests.  I also have to add more columns that it returns as well.  It is returning the correct results so far though.

Thank you very much for your help.
0
 

Author Comment

by:mattpayne59
ID: 35497085
One more issue... There can be multiple contact_names linked to one contact record.  Can I put them all under contact_name but separate them with a semicolon?

So I would need:

Contact_Name
Last1, First1;Last2, First2;etc

 I was just playing around for a bit to try and get it to do this but I am missing something.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35497270
in that case change line 33 above for this
stuff((select '; ' + description from CTE where first_name = t1.first and group_code = ''CONTCT'' for xml path('')), 1, 2, '') as Contact_name,

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35497635
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ' + description from CTE where first_name = t1.first and group_code = 'CONTCT' for xml path(')), 1, 2, ') as Contact_name,
                              '.

I added double ' around the semicolon and then I get:

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'as'.

I dont really understand the pivot so I am not sure what to fix here.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35498590
sorry, there's actually a bunch of quotes missing there. Check this now:
stuff((select ''; '' + description from CTE where first_name = t1.first and group_code = ''CONTCT'' for xml path('''')), 1, 2, '''') as Contact_name,

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35498971
t1.first is still giving me a problem.  I changed this to t1.first_name but I am not sure if that was correct or not.  Basically in this test there are 3 rows that are supposed to have a value in contact_name.  All 3 of them are showing all of the values.  So for example, it is showing:

Contact 1: Last1, First1;Last2,First2;Last3,First3;Last1First1
Contact 2: Last1, First1;Last2,First2;Last3,First3;Last1,First1
Contact 3: Last1, First1;Last2,First2;Last3,First3;Last1,First1

However, it should be:

Contact 1: Last1, First1;
Contact 2: Last1, First1;
Contact 3: Last1, First1;Last2,First2;Last3,First3

It is merging together any of the possibilities and duplicating the one that appears in the other contacts instead of splitting them out in the appropriate row it looks like?  Unless I somehow messed up what you meant by t1.first.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35499127
>>I changed this to t1.first_name but I am not sure if that was correct or not<<

Yes, you're correct. that was a bug in my above suggestion.

>>It is merging together any of the possibilities and duplicating the one that appears in the other contacts instead <<
That's because you need to add more conditions to uniquely link them together. Probably as you've mentioned above you need to add the legal_entity_sk column in there.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 35499133
missed the attachment above. Check the full query now:
declare @strSQL varchar(max)
declare @cols varchar(max)
declare @cols1 varchar(max)


set @cols1 = stuff((select distinct  ', case when [' + dbo.group_items.description + '] = 0 then ''N'' else ''Y'' end as [' + dbo.group_items.description + ']' 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '')


set @cols = stuff((select distinct  '], [' + dbo.group_items.description 
		from dbo.legal_entity
		INNER JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
		INNER JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
		where dbo.legal_entity.last_name = 'DePascale' and dbo.legal_entity.maint_user_id <> 'ExchSync' and dbo.legal_entity_picklist.group_code <> 'CONTCT'
		order by 1 for xml path('')), 1, 2, '') + ']'

set @strSQL = ';with CTE as (select 	dbo.legal_entity.first_name, 
	dbo.legal_entity.legal_entity_sk,									dbo.legal_entity_picklist.group_code, 
										dbo.group_items.description
							from dbo.legal_entity
							LEFT JOIN dbo.legal_entity_picklist ON dbo.legal_entity.legal_entity_sk=dbo.legal_entity_picklist.legal_entity_sk
							LEFT JOIN dbo.group_items ON dbo.legal_entity_picklist.group_item_sk=dbo.group_items.item_sk
							where dbo.legal_entity.last_name = ''DePascale'' and dbo.legal_entity.maint_user_id <> ''ExchSync''
				) 
				select first_name, ' + @cols1 + ', Contact_Name 
				from (
					select 	first_name, 
							group_code, 
							stuff((select ''; '' + description from CTE where first_name = t1.first_name and legal_entity_sk = t1.legal_entity_sk and group_code = ''CONTCT'' for xml path('''')), 1, 2, '''') as Contact_name,							description
					from CTE t1
				) o
				pivot (count(group_code) for description in (' + @cols + ')) as p'
		
exec(@strSQL)

Open in new window

0
 

Author Comment

by:mattpayne59
ID: 35500352
This looks good.  Can you explain the bottom a little bit and then I will close the question?  You have been very helpful.  I dont understand the strSQL at the bottom and why there are multiple select statements there that look similar.  I also dont understand what CTE and t1 mean there.  I understand @cols and @cols1 and how they fit in.

I have to add a bunch of columns to this and without understanding the bottom SQL statement I am not sure where to put them.  I added last_name and it worked once I added it in both places.

Thank you again.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35501502
sure, so basically the strSQL is a variable you're using to create a select query dynamically. In this case the pivot query. You can see the result of all this work by changing the last statement

exec(@strsSQL)

to

print @strSQL

This will show the end query that produces the result you want.

CTE stands for Common Table Expressions and is just being used to create an "in-line" view. The definition in this case is:

;with CTE as (
  select       ....
  from dbo.legal_entity
  LEFT JOIN ...
  LEFT JOIN ...
  WHERE ...
)

CTE starts with the "with" statement, then the name of your CTE query, in this case we called it "CTE" but you can use any other valid name. The beauty of it is that you can reference the query defined many times and that's exactly what we did here:

select first_name, ' + @cols1 + ', Contact_Name
                        from (
                              select       first_name,
                                          group_code,
                                          stuff((select ''; '' + description from CTE where first_name = t1.first_name and legal_entity_sk = t1.legal_entity_sk and group_code = ''CONTCT'' for xml path('''')), 1, 2, '''') as Contact_name,                                          description
                              from CTE t1


t1 is just an alias I'm giving to the outer reference to CTE so that you can then link it with the inner one in here:

stuff((select ... from CTE where first_name = t1.first_name .... group_code = ''CONTCT'' for xml path('''')), 1, 2, '''') as Contact_name,      

More info on CTE
http://msdn.microsoft.com/en-us/library/ms190766.aspx

Hope this helps!
0
 

Author Comment

by:mattpayne59
ID: 35505682
I am adding columns now and I got this when I added an address column.

Pivot grouping columns must be comparable. The type of column "address_1" is "text", which is not comparable.

Am I limited to what I can add while using pivot?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35505850
well, first of all, "text" datatype is not longer recommended. in SQL 2005 and above you should use varchar(max) instead. It will make your life easier down the road.

if you can't change your table, then try casting it as varchar(max)

so basically

...cast(address_1 as varchar(max)) ...
0
 

Author Comment

by:mattpayne59
ID: 35509818
It isnt my database but I was able to cast it.  I also joined in a few more tables to get some other values and completed my query.  Thank you again for your help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question