SQL - Combine results into single line?

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)

Select allOpen in new window

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.

ralmada, your example is much better.

Bye

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.

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?

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)

Select allOpen in new window

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)

Select allOpen in new window

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.

>>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)

Select allOpen in new window

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.

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.

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?

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'?
 

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.

Would it be possible to give some sample data from the tables with expected result?

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)

Select allOpen in new window

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.

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.

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,

Select allOpen in new window

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.

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,

Select allOpen in new window

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.

>>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.

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.

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!

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?

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)) ...

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!