Solved

SQL Results, not sure how to get what I want out of them

Posted on 2010-08-20
12
783 Views
Last Modified: 2012-05-10
Hi there,

I'm trying to get results from my SQL code but I can't seem to get them right. Here is my code:

select  tBlocking.WholeCompany, tBlocking.BlockingDate, tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName from tBlocking
LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID
LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID
LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID
LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID
LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID
LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID
LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID
WHERE tBlocking.BlockingDate > GetDate()

I have attached a worksheet with the results I get (yellow) and the ones I want (green).

As you can see the ones in yellow return multiple lines because there are more than one Partner assigned to that company. What I would like to have is when there are more, they show up as they do in green. THere is no max on how many Partners assigned to each company.

How can I get the desired effects?
SQLResultsGood.xls
0
Comment
Question by:PrimusPilus
  • 6
  • 3
  • 3
12 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33488871
Does each of the partners need to be in a seperate column, or can they be concatenated into one column labled "Partners"
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33489067
Try this:
 

select  tBlocking.WholeCompany

	, tBlocking.BlockingDate

	, tSearch.DESCRIPTION

	, tUser.Partners

	, tCompany.CompanyName

	, tBlocking.BlockingNotes

	, tPeople.FirstName

	, tPeople.LastName 

from tBlocking

	LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID

	LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID

	LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID

	LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID

	LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID

	LEFT JOIN (

			SELECT Main.GUID AS GUID, 

				  LEFT(Main.Partner,Len(Main.Partner)-1) As [Partners] 

			FROM(SELECT DISTINCT b.GUID,  

					 (SELECT a.FirstName + ' ' +  a.LastName + ',' AS [text()] 

					  FROM dbo.tUser a 

					  WHERE a.GUID = b.GUID 

					  ORDER BY a.GUID 

					  FOR XML PATH ('')) [Partner] 

				FROM dbo.tUser b) [Main] 

			) tUser ON tSearchResponsibility.UserGUID = tUser.GUID

	LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID

WHERE tBlocking.BlockingDate > GetDate()

Open in new window

0
 

Author Comment

by:PrimusPilus
ID: 33489309
Hi,

aelliso, it does not matter, they can all be in the same column!

I still get the same amount of lines with that one, it does not remove duplicate lines for companies.

Thanks for looking into this!
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33489444
Will SELECT DISTINCT work on the first row?
0
 

Author Comment

by:PrimusPilus
ID: 33489479
Hi there,

Select Distinct will not work!

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33489867
I missed one column, but you can add it back to the pattern.
declare @sql nvarchar(max)  

declare @columns1 varchar(max)

declare @columns2 varchar(max)

declare @maxcount int



select @maxcount=MAX(c) from

(select COUNT(distinct partner) c

 from #tmp

 group by WholeCompany,Description,Company,Notes,FirstName,LastName) x



select

	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',

	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'

from master..spt_values where type='p' and number between 1 and @maxcount



set @sql = '  

	SELECT WholeCompany,Description,Company'+@columns2+',Notes,FirstName,LastName

	FROM (SELECT *, RN=ROW_NUMBER() OVER (

		PARTITION BY WholeCompany,Description,Company,Notes,FirstName,LastName

		ORDER BY PARTNER)

		FROM (SELECT DISTINCT * FROM #TMP) A) B

	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  

EXEC (@sql)  

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:PrimusPilus
ID: 33504569
Thanks for the reply cyberiwi. I'm having trouble making sense of your code. What does the #tmp stand for?

I'm not very good with SQL, actually I'm at a basic level, but this looks like something I run after my code?

Thanks!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33505661
You need to put the data into a temp table first.
Create the proc in the code box, and run the singular line below whenever you need to view the data.

exec ViewME;
CREATE PROC ViewME

AS

select  tBlocking.WholeCompany, tBlocking.BlockingDate, tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName

into #tmp

from tBlocking

LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID

LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID

LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID

LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID

LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID

LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID

LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID

WHERE tBlocking.BlockingDate > GetDate();



declare @sql nvarchar(max)  

declare @columns1 varchar(max)

declare @columns2 varchar(max)

declare @maxcount int



select @maxcount=MAX(c) from

(select COUNT(distinct partner) c

 from #tmp

 group by WholeCompany,BlockingDate,Description,Company,Notes,FirstName,LastName) x



select

	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',

	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'

from master..spt_values where type='p' and number between 1 and @maxcount



set @sql = '  

	SELECT WholeCompany, BlockingDate, Description, Company'+@columns2+', Notes, FirstName, LastName

	FROM (SELECT *, RN=ROW_NUMBER() OVER (

		PARTITION BY WholeCompany, BlockingDate, Description, Company, Notes, FirstName, LastName

		ORDER BY PARTNER)

		FROM (SELECT DISTINCT * FROM #TMP) A) B

	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  

EXEC (@sql);

GO

Open in new window

0
 

Author Comment

by:PrimusPilus
ID: 33506391
Thank you Sir!

I think we are getting somewhere!

I get two error messages:

Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 421, Level 16, State 1, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
Msg 488, Level 16, State 1, Line 2
Pivot grouping columns must be comparable. The type of column "BlockingNotes" is "text", which is not comparable.

I had to remove the group by line as it gave me this error:

Msg 306, Level 16, State 2, Procedure ViewME, Line 20
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I assumed it might not be vital.....??

Thank you for you help!
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33506580
Ignore the null values warning
I assume varchar(100) is enough for BlockingDate?
CREATE PROC ViewME

AS

select  tBlocking.WholeCompany, convert(varchar(100),tBlocking.BlockingDate), tSearch.Description, tUser.FirstName + ' ' +  tUser.LastName as Partner, tCompany.CompanyName, tBlocking.BlockingNotes, tPeople.FirstName, tPeople.LastName

into #tmp

from tBlocking

LEFT JOIN tCompany on tBlocking.CompanyGUID = tCompany.GUID

LEFT JOIN tSearch ON tBlocking.SearchGUID = tSearch.GUID

LEFT JOIN tBlockingDetail ON tBlocking.GUID = tBlockingDetail.BlockingGUID

LEFT JOIN tPeople ON tBlockingDetail.PeopleGUID = tPeople.GUID

LEFT JOIN tSearchResponsibility ON tSearchResponsibility.SearchGUID = tSearch.GUID

LEFT JOIN tUser ON tSearchResponsibility.UserGUID = tUser.GUID

LEFT JOIN tRole ON tSearchResponsibility.RoleGUID = tRole.GUID

WHERE tBlocking.BlockingDate > GetDate();



declare @sql nvarchar(max)  

declare @columns1 varchar(max)

declare @columns2 varchar(max)

declare @maxcount int



select @maxcount=MAX(c) from

(select COUNT(distinct partner) c

 from #tmp

 group by WholeCompany,BlockingDate,Description,Company,Notes,FirstName,LastName) x



select

	@columns1 = coalesce(@columns1+',','') + '[' + CONVERT(varchar(10),number) + ']',

	@columns2 = coalesce(@columns2,'') + ',[' + CONVERT(varchar(10),number) + '] [Partner' + CONVERT(varchar(10),number) + ']'

from master..spt_values where type='p' and number between 1 and @maxcount



set @sql = '  

	SELECT WholeCompany, BlockingDate, Description, Company'+@columns2+', Notes, FirstName, LastName

	FROM (SELECT *, RN=ROW_NUMBER() OVER (

		PARTITION BY WholeCompany, BlockingDate, Description, Company, Notes, FirstName, LastName

		ORDER BY PARTNER)

		FROM (SELECT DISTINCT * FROM #TMP) A) B

	PIVOT (MAX([Partner]) FOR RN IN (' + @columns1 + ') ) AS pvt'  

EXEC (@sql);

GO

Open in new window

0
 

Author Comment

by:PrimusPilus
ID: 33513389
Thank you! That worked great!

0
 

Author Closing Comment

by:PrimusPilus
ID: 33513391
Great help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now